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/Timestamp Input

Supported Date/Time/Timestamp type string formats:

Date Syntax Time Syntax DATE TIME TIMESTAMP Output

mm/dd/yyyy

hh:mm:ss.SSSSSS

'09/08/2013'

'16:30:59.000005'

'09/08/2013 16:30:59.000005'

2013-09-08 16:30:59.000005

m/d/yyyy

h:m:s.SSS

'9/8/2013'

'4:3:2.003'

'9/8/2013 4:3:2.003'

2013-09-08 04:03:02.003

yyyy-mm-dd

hh:mi:ss

'2013-09-08'

'12:01:01'

'2013-09-08 12:01:01'

2013-09-08 12:01:01

yyyy.d.m

hh:mi:ss.SSSSSS

'2013.9.8'

'10:11:12.123456'

'2013.9.8 10:11:12.123456'

2013-08-09 10:11:12.123456

d.m.yy

hh:mi:ss

'8.9.13'

'10:11:12'

'8.9.13 10:11:12'

2013-09-08 10:11:12

Month dd, yyyy

hh:mi

'September 08, 2013'

'14:55'

'September 08, 2013 14:55'

2013-09-08 14:55:00

Month d, yy

hh:mi

'September 8, 13'

'12:59'

'September 8, 13 12:59'

2013-09-08 12:59:00

Mon d yy

hh:mi

'Sept 8 14'

'14:00'

'Sept 8 14 14:00'

2013-09-08 14:00:00

Mon dd, yyyy

h:mi [AM|PM]

'Sep 08 2013'

'1:00 PM'

'Sep 08 2013 1:00 PM'

2013-09-08 13:00:00

dd/Mon/yyyy

hh:mi [AM|PM]

'08/Sep/2013'

'02:00 AM'

'08/Sep/2013 02:00 AM'

2013-09-08 02:00:00

d/Mon/yy

hh:mm:ss.SSS

'8/Sept/13'

'01:00:00.001'

'8/Sept/13 01:00:00.001'

2013-09-08 01:00:00.001

mm/dd/yy

``

'12/31/70'

``

'12/31/70'

2070-12-31 00:00:00

mm/dd/yy

``

'01/01/71'

``

'01/01/71'

1971-01-01 00:00:00

yyyyMMdd

'20191231'

2019-12-31 00:00:00

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