Fixed timestamps represent an exact point on the time-line, limited to millisecond precision, encoded as ISO8601 Times, with a timezone offset. The timezone offset returned will always be expressed in UTC (Coordinated Universal Time), with a special UTC designator (“Z”). For example:
[ {
"date_time_column": "2014-10-13T00:00:00.000Z"
} ]When writing data, accuracy to only the second is required, but the service will always return precision to the millisecond. Additionally, you can use offsets like -0800 (or -08:00). Using this offset system, America/Los_Angeles is expressed as UTC-08:00, or UTC-07:00 in the summer. The offset -08:00 indicates that America/Los_Angeles time is obtained from UTC by adding -08:00, that is, by subtracting 8 hours. Accordingly, 2020-10-16T18:00:00.000Z and 2020-10-16T10:00:00.000-0800 are two different but equally valid representations of the same fixed_timestamp value.
If you are a data publisher, please note that the SODA Producer API converts values with non-UTC offsets into the equivalent UTC representation of the value, so if you write a value like 2020-10-16T10:00:00.000-0800 to a fixed_timestamp field, the SODA Consumer API will return that value as 2020-10-16T18:00:00.000Z.
The following operators can be used to compare and manipulate fixed_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 |
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 rows that were created before October 16, 2020 at 06:00:00 AM, we can query on the :created_at system field, which is a fixed_timestamp type:
Text strings will be automatically be cast when used in comparisons, as shown above.