How to query N1QL datetime


#1

i use sql in localhost can result
online not reuslt
select log.* from log where date >= ‘2016-03-14 10:45:00’ and date <‘2016-03-14 11:55:00’ limit 15

datetime can use ?


#2

@edobnet

To work with date/time values in N1QL, it’s usually best to deal with ISO 8601 datetime values. If they’re encoded in the database that way, then you can use STR_TO_MILLIS() to convert them to Unix milliseconds to do comparisons.

For example:

select log.* from log where STR_TO_MILLIS(date) >= STR_TO_MILLIS('2016-03-14T10:45:00Z') and STR_TO_MILLIS(date) < STR_TO_MILLIS('2016-03-14T11:55:00Z') limit 15

If you know that you’re always in the same time zone, i.e. UTC, then you don’t have to use the STR_TO_MILLIS. But you do need to make sure your string constants in the query are in ISO 8601 format to match the values in the document.

Brant