Datetime conversions in couchbase

SQL++
1

Hi Team,
Our DB has datetime details in format - “Dt”: “/Date(1488344400000-0500)/” , how can i convert it to datetime format and compare with other date time values.

2

Couchbase datetime follows ISO 8601 format and string comparable. We have many datetime functions https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

What is 1488344400000 ? is this milliseoncds? -0500 Timezone.

cc @isha

3

yes its in millisecs

4

You can extract millis and compare As follows. But taking timezone into consideration in millis make it complex.
You can covert the Dt into ISO 8601 format before inserting data into couch base in the application.

select TO_NUMBER(SUBSTR(Dt,6,LENGTH(Dt)-13)) millis, MILLIS_TO_UTC(TO_NUMBER(SUBSTR(Dt,6,LENGTH(Dt)-13))) date FROM default;