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.
Date/Time Minimum and Maximum Supported Values
| Limit | Value | Exceptions |
|---|---|---|
Minimum |
0001-01-01 00:00:00 |
When reading the minimum date/time value from the database, if the UTC offset of the database connection reading the value is less than the UTC offset of the database connection that wrote the value to the database the Transaction Engine (TE) will assert. If the application is required to work with date or timestamp values before the year 1972, it is important to always use the UTC timezone for both the NuoDB database and the application connection settings. For more details, see Connection Properties. NOTE: Date and timestamp values before 1972 may behave unexpectedly in regards to timezones and DST settings before the definition of UTC. |
Maximum |
9999-12-31 23:59:59 |
None |
Date/Time/Timestamp Input
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:21Time 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 implicitly, 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