Floating Timestamp Datatype

Floating timestamps represent an instant in time with millisecond precision, with no timezone value, encoded as ISO8601 Times with no timezone offset. When writing data, accuracy to only the second is required, but the service will always return precision to the millisecond. For example:

[ {
  "date_time_column": "2014-10-13T00:00:00.000"
} ]

Datasets will either specify what timezone they should be interpreted in, or you can usually assume they’re in the timezone of the publisher. For example, a dataset published by the City of Chicago will be published in Central Standard Time.

The following operators can be used to compare and manipulate floating_timestamp fields:

Operation Description
< TRUE when the first date is earlier than the second date
<= TRUE when the first date is earlier than or at the same time as the second date
> TRUE when the first date is after the second date
>= TRUE when the first date is after or at the same time as the second date
!= TRUE when two dates are not at the same time
= TRUE when two dates are at the same time
IS NULL TRUE for dates that are NULL.
IS NOT NULL TRUE for dates that are not NULL.

And the following functions can be used to filter and manipulate them:

Function Name Description Availability
between ... and ... Returns TRUE for values in a given range 2.1
case(...) Returns different values based on the evaluation of boolean comparisons 2.1
count(...) Returns a count of a given set of records 2.0 and 2.1
date_trunc_y(...) Truncates a calendar date at the year threshold 2.0 and 2.1
date_trunc_ym(...) Truncates a calendar date at the year/month threshold 2.0 and 2.1
date_trunc_ymd(...) Truncates a calendar date at the year/month/date threshold 2.0 and 2.1
in(...) Matches values in a given set of options 2.1
max(...) Returns the maximum of a given set of numbers 2.1
min(...) Returns the minimum of a given set of numbers 2.1
not between ... and ... Returns TRUE for values not in a given range 2.1
not in(...) Matches values not in a given set of options 2.1

For example, to get all of the crimes that occurred between noon and 2PM on January 10th, 2015 in Chicago:

https://data.cityofchicago.org/resource/6zsd-86xi.json?$where=date between '2015-01-10T12:00:00' and '2015-01-10T14:00:00'

Text strings will be automatically be cast when used in comparisons, as shown above.