When data ingested into or returned from Rockset in JSON form, these types can be specified in a special format shown below.
{
"_id": "foo"
"_event_time": {"__rockset_type": "timestamp", "value": "42"}
}
Note that field _event_time is parsed not as object but rather timestamp with value 42.
DATE
date
A date value represents a logical calendar date (year, month, day) independent of time zone. A date does not represent a specific time period; it can differ based on timezones. To represent an absolute point in time, use a timestamp instead.
A date literal in SQL syntax is formatted as follows.
DATE 'YYYY-[M]M-[D]D'
This consists of:
YYYY: Four-digit year[M]M: One or two digit month[D]D: One or two digit day
DATE '2018-01-01' -- example literal
DATE(2018, 1, 1) -- constructor function
DATETIME
datetime
A datetime value represents a point in time (year, month, day, hour, minute, second, microsecond). It does not refer to an absolute instance in time, unlike timestamp. Instead, it is the civil time; the time that a user would see on a watch or calendar.
A date literal in SQL syntax is formatted as follows.
DATETIME 'YYYY-[M]M-[D]D[( )[H]H:[M]M:[S]S[.DDDDDD]]'
This consists of:
YYYY: Four-digit year[M]M: One or two digit month[D]D: One or two digit day( ): A space separator[H]H: One or two digit hour (valid values from 00 to 23)[M]M: One or two digit minutes (valid values from 00 to 59)[S]S: One or two digit seconds (valid values from 00 to 59)[.DDDDDD]: Up to six fractional digits
DATETIME '2018-01-01 9:30:45.456' -- example literal
DATETIME(2018, 1, 1, 9, 30, 45, 456) -- constructor function
TIME
time
A time value represents the time of the day (hour, minute, second, millisecond) independent of a specific date.
A time literal in SQL syntax is formatted as follows.
TIME '[H]H:[M]M:[S]S[.DDDDDD]'
This consists of:
[H]H: One or two digit hour (valid values from 00 to 23)[M]M: One or two digit minutes (valid values from 00 to 59)[S]S: One or two digit seconds (valid values from 00 to 59)[.DDDDDD]: Up to six fractional digits
TIME '09:30:45.456' -- example literal
TIME(9, 30, 45, 456) -- constructor function
TIMESTAMP
timestamp
A timestamp value represents absolute date and time values independent of any time zone.
A timestamp literal in SQL syntax is formatted as follows.
TIMESTAMP 'YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]'
This consists of:
YYYY: Four-digit year[M]M: One or two digit month[D]D: One or two digit day( ): A space separator[H]H: One or two digit hour (valid values from 00 to 23)[M]M: One or two digit minutes (valid values from 00 to 59)[S]S: One or two digit seconds (valid values from 00 to 59)[.DDDDDD]: Up to six fractional digits[time zone]: Offset from Coordinated Universal Time (UTC). When a time zone is not explicitly
specified, the default time zone, UTC, is used. The offset is formatted as(+|-)H[H][:M[M]], or simplyZto refer to UTC. When using this format, no space is allowed between the time zone and the rest of the timestamp.
TIMESTAMP '2018-01-01 09:30:45.456-05:00' -- example literal
MONTH INTERVAL
month_interval
A month interval refers to a specific number of months.
As months have different lengths, month intervals may only be added to or subtracted from date or datetime values.
Examples of month intervals are shown below.
INTERVAL 3 MONTH;
INTERVAL 2 YEAR;
INTERVAL '2-3' YEAR TO MONTH;
MICROSECOND INTERVAL
microsecond_interval
A microsecond interval refers to a fixed amount of time with microsecond precision.
Microsecond intervals may be added to or subtracted from dates, times, datetimes, and timestamps. Also, you get a microsecond interval when you subtract two dates, times, datetimes, or timestamps (indicating the length of time between the two time points).
Examples of microsecond intervals are shown below.
INTERVAL 2 DAY;
INTERVAL 3 HOUR;
INTERVAL 5 MINUTE;
INTERVAL 10 SECOND;
INTERVAL '2 10:23:45.56' DAY TO SECOND;