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_
On this page
The BSON_ 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.
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 SingleStoreDATETIME(6)type.Otherwise, NULLis returned. -
If the value at the specified
<path>is not a supported BSON type or the specified<path>does not exist,NULLis 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: