Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

BSON_EXTRACT_DATETIME

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:

SQL

SELECT BSON_EXTRACT_DATETIME('{"$date":"1234-05-28T21:35:22.567Z"}') AS Result;

+----------------------------+
| Result                     |
+----------------------------+
| 1234-05-28 21:35:22.567000 |
+----------------------------+

SQL

SELECT BSON_EXTRACT_DATETIME('{"$oid":"0a01579f5d884c1735b882be"}') AS Result;

+----------------------------+
| Result                     |
+----------------------------+
| 1975-04-27 19:48:47.000000 |
+----------------------------+

SQL

SELECT BSON_EXTRACT_DATETIME('{"$timestamp":{"i":8,"t":1679525146}}') AS Result;

+----------------------------+
| Result                     |
+----------------------------+
| 2023-03-22 22:45:46.000000 |
+----------------------------+