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. While functionally a floating_timestamp is distinct from a text datatype, it may be helpful to think of the value of a floating_timestamp as simply a text string, with no inherent timezone information.
The following operators can be used to compare and manipulate floating_timestamp fields:
| Operator | 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:
| Keyword Name | Description | Availability |
|---|---|---|
distinct | Returns distinct set of records | 2.1 |
| 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_extract_d(...) | Extracts the day from the date as an integer. | 2.1 |
date_extract_dow(...) | Extracts the day of the week as an integer between 0 and 6 (inclusive). | 2.1 |
date_extract_hh(...) | Extracts the hour of the day as an integer between 0 and 23 (inclusive). | 2.1 |
date_extract_m(...) | Extracts the month as an integer. | 2.1 |
date_extract_mm(...) | Extracts the minute from the time as an integer. | 2.1 |
date_extract_ss(...) | Extracts the second from the time as an integer. | 2.1 |
date_extract_woy(...) | Extracts the week of the year as an integer between 0 and 51 (inclusive). | 2.1 |
date_extract_y(...) | Extracts the year as an integer. | 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 |
greatest(...) | Returns the largest value among its arguments, ignoring NULLs. | 2.1 |
in(...) | Matches values in a given set of options | 2.1 |
least(...) | Returns the smallest value among its arguments, ignoring NULLs. | 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:
Text strings will be automatically be cast when used in comparisons, as shown above.