SQL Date and Time Types
This section provides details about Date and Time data types in NuoDB: input values, timestamps, literals and time zones. NuoDB also provides support for DATE
and TIME
formatting and conversion on output. See SQL Date and Time Functions and Operators.
Date/Time Types
Name | Description |
---|---|
DATE |
date only (no time of day) |
TIME [ ( p ) ] |
time of day only (no time zone) |
TIMESTAMP [ ( p ) ] |
both date and time of day (no time zone) |
TIME and TIMESTAMP accept an optional precision value p which specifies the fraction of a second to be stored. The allowed range of p is from 0 to 9. The default precision for TIME is 0. The default precision for TIMESTAMP is up to 9. Trailing zeros are dropped. If no precision is specified in the column type, the value stored defaults to the precision given in the string literal being inserted.
Supported Date/Time/Timestamp type string formats:
Date Syntax | Time Syntax | DATE | TIME | TIMESTAMP | Output |
---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
`` |
|
`` |
|
|
|
`` |
|
`` |
|
|
|
|
|
Literals
The DATE, TIME, TIMESTAMP keywords can be used to define literals of temporal type (DATE, TIME or TIMESTAMP). Using this format allows for finer control of the data types used in SQL expressions (rather than relying on implicit type conversions). All the string formats are accepted as a valid format for a temporal literal.
For example:
select TIME'20:01:21', DATE'2010-01-01', TIMESTAMP'2010-01-01 20:01:21' from dual;
-------- ---------- -------------------
20:01:21 2010-01-01 2010-01-01 20:01:21
Time Zone
TIME
and TIMESTAMP
are persisted as points in time (as seconds either before or after the epoch 1970-01-01 UTC). When converting from a string value to a time/timestamp value, NuoDB uses the connection time zone (either set explicitly or implictily, see Connection Properties for details) to compute the offset of the timezone since Unix Epoch UTC. This is to say that both TIME
and TIMESTAMP
behave as SQL Standard WITH TIME ZONE
, with the time zone implicitly defined by the connection manipulating the temporal type.
Aditionally, the time zone can be explicitly specified in string literals. For example, for a connection with time zone set to Europe/Athens, the following behaviour is expected:
select timestamp'2010-01-01 00:00:01 UTC' from dual;
-------------------
2010-01-01 02:00:01