Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

BSON_EXTRACT_DATETIME

The BSON_EXTRACT_DATETIME() function 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

Note

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.

Syntax

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

Arguments

  • <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.

Remarks

  • 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.

Examples

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:

Was this article helpful?