Extracts the value for the specified key from the BSON data, converts it to a SingleStore DATETIME(6) type, and returns the value.

This function accepts the following BSON types:

  • Datetime

  • Timestamp

  • ObjectId


The date and time range supported by SingleStore DATETIME(6) type differs from BSON datetime type. Refer to BSON Date Type and SingleStore Time and Date Type for more information.


BSON_EXTRACT_DATETIME(<input> [, path_key_1, path_key_2, ..., path_key_n])


  • <input>: A supported BSON type value, a valid BSON document, or an expression that evaluates to a valid BSON value or document.

  • <path>: A comma-separated list of dictionary keys or array positions (zero-based index).

Return Type

SingleStore DATETIME(6) type.


  • If the <path> argument is not specified and the <input> is a BSON value of type datetime, timestamp or ObjectId, the <input> is converted to its equivalent SingleStore DATETIME(6) type. Otherwise, NULL is returned.

  • If the value at the specified <path> is not a supported BSON type or the specified <path> does not exist, NULL is returned.


The following examples extract datetime types:

SELECT BSON_EXTRACT_DATETIME('{"$date":"1234-05-28T21:35:22.567Z"}') AS Result;
| Result                     |
| 1234-05-28 21:35:22.567000 |
SELECT BSON_EXTRACT_DATETIME('{"$oid":"0a01579f5d884c1735b882be"}') AS Result;
| Result                     |
| 1975-04-27 19:48:47.000000 |
SELECT BSON_EXTRACT_DATETIME('{"$timestamp":{"i":8,"t":1679525146}}') AS Result;
| Result                     |
| 2023-03-22 22:45:46.000000 |

Last modified: April 2, 2024

Was this article helpful?