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

Date and time input is accepted in almost any reasonable format. Any date or time input literal must be enclosed in single quotes, like text strings. The DATE/TIME inputs are converted using the following rules:

  1. A numeric input string for a TIME field must contain one or more ":". Optionally, AM or PM is supported. There are three interpretations:
    • Hour:Minute
    • Hour:Minute:Second
    • Hour:Minute:Second.FractionalSecond, where precision of a second is 0 - 9.
  2. A numeric input string for a DATE field must contain any two of "-", "/" or ".".
    • a numeric DATE input containing "-" or "/" has three interpretations:
      • Year(4 digits)-Month-Day
      • Month-Day-Year (4 digits)
      • Month-Day-Year (2 digits)
    • a numeric DATE input containing "." has two interpretations:
      • Year(4 digits).Day.Month
      • Day.Month.Year(2 digits)
  3. A DATE string will contain a month represented by either a numeric value or a text string (i.e. SEPTEMBER, SEPT, SEP). If Month is represented by a text string, interpretations of Year(2 digits)-Month-Day and Day-Month-Year(2 or 4 digits) are also supported.
  4. Valid input for TIMESTAMP types consists of a concatenation of a DATE and a TIME input, separated by a whitespace, optionally followed by AM or PM.
  5. Two digit years falling between 00-70 are resolved as 2000-2070. Two digit years from 71-99 are resolved as 1971-1999.

Examples of Date/Time/Timestamp type input:

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

Literals

NuoDB supports special DATE/TIME input values for convenience. They are simply notational shorthands that will be converted to ordinary DATE/TIME values when read. All of these values need to be written in single quotes when used as constants in SQL statements.

Input String Valid Types Description
now DATE, TIME, TIMESTAMP the specific date and/or time value as soon as it is read
today DATE, TIMESTAMP midnight today
tomorrow DATE, TIMESTAMP midnight tomorrow
yesterday DATE, TIMESTAMP midnight yesterday
thisday
thismonth
thisyear
When used in combination;
DATE, TIMESTAMP
midnight today

Note: In addition, the following SQL-compatible functions can also be used to obtain the current date and/or time value for the corresponding data type: CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP Since these are SQL functions, and not data input values, they do not require single quotes. See SQL Date and Time Functions and Operators for a detailed description of these SQL functions.

Time Zone

TIME and TIMESTAMP are persisted as points in time (as seconds either before or after the epoch 1970-01-01 UTC). By default, each connection or session obtains time zone information from the client's current operating environment. Alternatively, the time zone for the client to use can also be specified as a property on the database connection e.g., jdbc:com.nuodb://localhost/quickstart?TimeZone=America/Chicago. See Connection Properties.

Both TIME and TIMESTAMP behave as if defined WITH TIME ZONE even though NuoDB does not support the WITH TIME ZONE and WITHOUT TIME ZONE clauses of the SQL Standard.

TIME can be converted to any time zone, regardless of the operating environment. See SQL Date and Time Functions and Operators for a list of functions to convert time zone on output. As an example:

CREATE TABLE timezone_table (c_timestamp TIMESTAMP);
INSERT INTO timezone_table VALUES ('2001/01/01 02:00:00.123456');
 
SELECT c_timestamp, convert_tz(c_timestamp,'EST','PST') AS PACIFIC, convert_tz(c_timestamp,'PST','EST') AS EASTERN 
 FROM timezone_table;
        C_TIMESTAMP                  PACIFIC                    EASTERN          
 -------------------------- -------------------------- --------------------------
 2001-01-01 02:00:00.123456 2000-12-31 23:00:00.123456 2001-01-01 02:00:00.123456 
 
/* Select the UNIX time or POSIX time */
SELECT c_timestamp,
       CAST(c_timestamp AS INTEGER) AS CONVERT_TO_INTEGER,
       CAST(c_timestamp AS DECIMAL(16,6)) AS CONVERT_TO_DECIMAL
 FROM timezone_table;
        C_TIMESTAMP         CONVERT_TO_INTEGER  CONVERT_TO_DECIMAL 
 -------------------------- ------------------- -------------------
 2001-01-01 02:00:00.123456      978332400       978332400.123456  
  
/* Insert a timestamp value using the UNIX time or POSIX time */
INSERT INTO timezone_table VALUES (978332400.123456);
 
SELECT * FROM timezone_table;
       C_TIMESTAMP        
 --------------------------
 2001-01-01 02:00:00.123456
 2001-01-01 02:00:00.123456

Time Zone Setting Details by Operating Environment

O/S Local Time Zone Settings How to find your current timezone
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 7/8/10 Windows 7/8/10 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

Windows 7:

Click Start > Control Panel > Clock, Language, and Region, then click Date and Time.


Windows 8:
In the Desktop taskbar, right click the clock and choose Adjust Date/Time

Using the same data from the previous SQL example, now change the time zone setting in the operating environment, for example from Eastern to Pacific and select the data:

/* same select as above, only time zone on operating environment has changed from Eastern to Pacific */
/* the timestamp display output is now adjusted for the timezone change (column C_TIMESTAMP) */
/*      but the UNIX time or POSIX time has not changed */
SELECT c_timestamp,
       CAST(c_timestamp AS INTEGER) AS CONVERT_TO_INTEGER, 
       CAST(c_timestamp AS DECIMAL(16,6)) AS CONVERT_TO_DECIMAL
 FROM timezone_table;
        C_TIMESTAMP         CONVERT_TO_INTEGER  CONVERT_TO_DECIMAL 
 -------------------------- ------------------- -------------------
 2000-12-31 23:00:00.123456      978332400       978332400.123456  
 2000-12-31 23:00:00.123456      978332400       978332400.123456

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 ('now','now','now');
    COL1      COL2              COL3           
 ---------- -------- --------------------------
 2014-01-14 14:32:46 2014-01-14 14:32:46.967036
 
INSERT INTO tst_date_time VALUES ('tomorrow',null,'today');
    COL1     COL2         COL3        
 ---------- ------ ------------------- 
 2014-01-15 <null> 2014-01-14 00:00:00
 
INSERT INTO tst_date_time VALUES ('thisyear'||'-'||'thismonth'||'-'||'thisday', NULL,
                                  'thisyear'||'-'||'thismonth'||'-'||'thisday');                                                                      
    COL1     COL2         COL3        
 ---------- ------ -------------------
 2014-01-14 <null> 2014-01-14 00:00:00
 
/* Examples to illustrate date and time default values */
CREATE TABLE tst_date_def (col1 DATE NOT NULL DEFAULT 'today'
                         , col2 TIME NOT NULL DEFAULT 'now'
                         , col3 TIMESTAMP NOT NULL DEFAULT 'yesterday');
 
INSERT INTO tst_date_def DEFAULT VALUES;
    COL1      COL2          COL3        
 ---------- -------- -------------------
 2014-01-14 14:40:21 2014-01-13 00:00:00