BSON_VALUE_TIMESTAMP function

The BSON_VALUE_TIMESTAMP function is a built-in SQL function that converts a time stamp in a BSON field to the built-in DATETIME data type.

Syntax

BSON_VALUE_TIMESTAMP function

>>-BSON_VALUE_TIMESTAMP--(--bson_column--,--"--field--"--)-----><


Element	Description	Restrictions	Syntax
`bson_column`	Name of a BSON column	Must exist	Identifier
`field`	A string that represents a field name to search for in the BSON column. Can be a multilevel identifier.		Quoted String, Column Expressions

Usage

Use the BSON_VALUE_TIMESTAMP function to return or operate on time stamp data in a field in a BSON column with SQL statements.

Example

The following statements create and populate a BSON column, and then convert the time stamps in the when field to DATETIME data:

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table VALUES('{id:36000,when:"12:09:2015"}'::JSON::BSON);
INSERT INTO bson_table VALUES('{id:36001,when:"09:23:2015"}'::JSON::BSON);
INSERT INTO bson_table VALUES('{id:36002,when:"05:18:2015"}'::JSON::BSON);

SELECT BSON_VALUE_TIMESTAMP(bson_col,"when") FROM bson_table;

     (expression)   
             12:09:2015
             09:23:2015
             05:18:2015

3 row(s) retrieved.