BSON_
On this page
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: April 2, 2024