BSON_
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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