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

TIMESTAMP [ ( p ) ]

both date and time of day (with time zone)

TIMESTAMP [ ( p ) ] WITHOUT TIME ZONE

both date and time of day (no time zone)
The definition of TIME and TIMESTAMP data types supports an optional precision value p which specifies the fraction of a second to be used for the data type. This is supported only at the syntax level (for backwards compatibility) but the fraction specified is ignored. TIMESTAMP and TIME can be stored with up to microsecond precision by default.

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 time zone 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 time zones 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

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
TIMESTAMP formats also apply for TIMESTAMP WITHOUT TIME ZONE

Literals

The DATE, TIME, TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE keywords can be used to define literals of their respective temporal types. 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', TIMESTAMP WITHOUT TIME ZONE'2010-01-01 20:01:21' from dual;
 [20:01:21]  [2010-01-01]  [2010-01-01 20:01:21]  [2010-01-01 20:01:21]
 ----------- ------------- ---------------------- ----------------------

  20:01:21    2010-01-01    2010-01-01 20:01:21    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 implicitly, see Connection Properties for details) to compute the offset of the time zone 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]
 ----------------------
  2010-01-01 02:00:01

This shows the timestamp value in UTC as offseted by the Europe/Athens time zone.

TIMESTAMP WITHOUT TIME ZONE is not affected by the connection time zone, as defined by the SQL standard. Converting a TIMESTAMP WITHOUT TIME ZONE value to a string value will yield the same result, regardless of the connection time zone.

If a time zone is specified in a TIMESTAMP WITHOUT TIME ZONE literal, it will effectively be ignored. For example:

select timestamp without time zone'2010-01-01 00:00:01 UTC' from dual;
 [2010-01-01 00:00:01]
 ----------------------
  2010-01-01 00:00:01

select timestamp without time zone'2010-01-01 00:00:01 Europe/Athens' from dual;
 [2010-01-01 00:00:01]
 ----------------------
  2010-01-01 00:00:01

Time Zone Setting Details by Operating Environment

O/S Local Time Zone Settings How to find your current time zone

Linux

Available time zones are located in /usr/share/zoneinfo directory of your system. In general, the Time Zone Database naming conventions should be used. If not already on your system, time zone data and code can be downloaded from the IANA site by doing something similar to the following:

mkdir tz
cd tz
wget --retr-symlinks 'ftp://ftp.iana.org/tz/tz*-latest.tar.gz'
gzip -dc tzcode-latest.tar.gz | tar -xf -
gzip -dc tzdata-latest.tar.gz | tar -xf -

Run the following to display the local time zone and time zone offset:

$ echo `date +%Z%z`
EST-0500

Windows 10

Windows contains a Time Zone Utility (tzutil) to manage available time zones and set the current time zone. See Microsoft TechNet Library for details on the tzutil command. Run the utility from a command prompt (pipe output to "more" to easily scroll, one screen at a time):

tzutil /l | more

In the Desktop taskbar, right-click the Date and Time display, choose "Adjust Date/Time" option and view "Time Zone" field value.

Examples

Date/Time/Timestamp Input

/* create a table with all data types to illustrate date and time input */
CREATE TABLE tst_date_time (col1 DATE, col2 TIME, col3 TIMESTAMP);

INSERT INTO tst_date_time VALUES ('09/08/2013','16:30:59.000005','09/08/2013 16:30:59.000005');
    COL1         COL2                  COL3
 ---------- --------------- --------------------------
 2013-09-08 16:30:59.000005 2013-09-08 16:30:59.000005

INSERT INTO tst_date_time VALUES ('2013.9.8','10:11:12.123456','2013.9.8 10:11:12.123456' );
   COL1         COL2                  COL3
 ---------- --------------- --------------------------
 2013-08-09 10:11:12.123456 2013-08-09 10:11:12.123456

INSERT INTO tst_date_time VALUES ('8.9.13','10:11:12','8.9.13 10:11:12');
   COL1      COL2          COL3
 ---------- -------- -------------------
 2013-09-08 10:11:12 2013-09-08 10:11:12

INSERT INTO tst_date_time VALUES ('Sept 8 14','14:00','Sept 8 14 14:00');
    COL1      COL2          COL3
 ---------- -------- -------------------
 2014-09-08 14:00:00 2014-09-08 14:00:00

INSERT INTO tst_date_time VALUES ('September 8, 13','12:59','September 8, 13 12:59');
    COL1      COL2          COL3
 ---------- -------- -------------------
 2013-09-08 12:59:00 2013-09-08 12:59:00

INSERT INTO tst_date_time VALUES ('Sep 08 2013','1:00 PM','Sep 08 2013 1:00 PM');
    COL1      COL2          COL3
 ---------- -------- -------------------
 2013-09-08 13:00:00 2013-09-08 13:00:00

INSERT INTO tst_date_time VALUES ('08/Sep/2013','02:00 AM','08/Sep/2013 02:00 AM');
    COL1      COL2          COL3
 ---------- -------- -------------------
 2013-09-08 02:00:00 2013-09-08 02:00:00

INSERT INTO tst_date_time VALUES ('12/31/70',null,'01/01/71');
    COL1     COL2         COL3
 ---------- ------ -------------------
 2070-12-31 <null> 1971-01-01 00:00:00

INSERT INTO tst_date_time VALUES (current_date,current_time,current_timestamp);
    COL1      COL2              COL3
 ---------- -------- --------------------------
 2014-01-14 14:32:46 2014-01-14 14:32:46.967036

INSERT INTO tst_date_time VALUES (date_add(current_date, interval 1 day),null,current_date);
    COL1     COL2         COL3
 ---------- ------ -------------------
 2014-01-15 <null> 2014-01-14 00:00:00

INSERT INTO tst_date_time VALUES ('20191231','10:11:12','2019-12-31 10:11:12');
    COL1     COL2         COL3
 ---------- -------- -------------------
 2019-12-31 10:11:12 2019-12-31 10:11:12


/* Examples to illustrate date and time default values */
CREATE TABLE tst_date_def (col1 DATE NOT NULL DEFAULT current_date
                         , col2 TIME NOT NULL DEFAULT current_time
                         , col3 TIMESTAMP NOT NULL DEFAULT date_sub(current_date, interval 1 day));

INSERT INTO tst_date_def DEFAULT VALUES;
    COL1      COL2          COL3
 ---------- -------- -------------------
 2014-01-14 14:40:21 2014-01-13 00:00:00

/* Example to illustrate the difference between TIMESTAMP and TIMESTAMP WITHOUT TIME ZONE */
CREATE TABLE tst_ts_tsnotz(ts TIMESTAMP, tsnotz TIMESTAMP WITHOUT TIME ZONE);

SET TIME ZONE 'Europe/London';

INSERT INTO tst_ts_tsnotz values('2022-01-01 10:00:00', '2022-01-01 10:00:00');

SELECT * FROM tst_ts_tsnotz;
         TS                TSNOTZ
 ------------------- -------------------

 2022-01-01 10:00:00 2022-01-01 10:00:00

SET TIME ZONE 'America/New_York';

SELECT * FROM tst_ts_tsnotz;
         TS                TSNOTZ
 ------------------- -------------------

 2022-01-01 05:00:00 2022-01-01 10:00:00