You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > SQL Date and Time Functions and Operators

SQL Date and Time Functions and Operators

NuoDB provides functional support for date and time value processing.

Date/Time Operators

Addition and subtraction operations may be performed on DATE and TIMESTAMP values. The table below illustrates this functionality. Input may be of any of the following:

Operator Description Example Result
+ Adding integer to a date/time literal select DATE('now') + 7 from dual; Today's date + 7 days
+ Adding integer to a date/time function select NOW() + 7 from dual; Today's date/time + 7 days
- Subtracting a date literal from a date/time function select NOW() - DATE('2013-10-16') from dual; Difference between two dates
- Subtracting integer from a date/time literal select DATE('now') - 7 Today's date - 7 days

Date/Time Functions

The following table presents date and time value processing functions.

Function Return Type Description Example
CONVERT_TZ(dt , from_tz, to_tz) TIMESTAMP CONVERT_TZ converts a Date/Time value dt from timezone from_tz to timezone to_tz. dt can be any table column, a string literal representing a date/time type (See SQL Date and Time Types), a date/time function, or any other expression resulting in a date/time type. For details about how to specify time zones, see SQL Date and Time Types. SELECT CONVERT_TZ(NOW(),'America/New_York','Asia/Tokyo') FROM DUAL;
SELECT CONVERT_TZ(NOW(),'EST','PST') FROM DUAL;
CURRENT_DATE DATE Returns the date in the format YYYY-MM-DD of the start of the current transaction. SELECT CURRENT_DATE FROM DUAL;
CURRENT_TIME
[([p])]
TIME Returns the time in the format HH:MM:SS of the start of the current transaction. Fractional seconds are optional and may be specified by the precision value enclosed in parentheses. Values are 0-9. The default, if not provided, is 0. Trailing zeros are dropped from fractional seconds. SELECT CURRENT_TIME FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;
SELECT CURRENT_TIME(3) FROM DUAL;
CURRENT_TIMESTAMP
[()]
TIMESTAMP Returns the timestamp of the start of the current transaction. See NOW() below for current timestamp. Fractional seconds are displayed with a precision of up to 9. Trailing zeros are dropped from fractional seconds. SELECT CURRENT_TIMESTAMP FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
DATE (string) DATE Converts a string to a DATE data type. If string is a TIMESTAMP literal, the TIME is truncated. A string can also be a date literal (see SQL Date and Time Types for a list of date literals). SELECT DATE('02-01-2013 01:00') FROM DUAL;
or
SELECT DATE('tomorrow') FROM DUAL;
DATE_ADD ( input_value , INTERVAL quantity_expr unit_of_time ) DATE or TIMESTAMP Adds some unit of time to a date/time input value; See DATE_ADD below See DATE_ADD function below
DATEDIFF (interval, start_date, end_date) INTEGER Returns an integer value representing the time between two dates in <interval> units. The return value represents the specified <interval> boundaries crossed between the specified start_date and end_date. See DATEDIFF function section below
DATEINTERVAL (interval, start_date, end_date) DECIMAL Returns a decimal value representing the time between two dates in <interval> units. The return value represents the specified <interval> boundaries crossed between the specified start_date and end_date including fractional component. See DATEINTERVAL function section below
DATE_SUB(input_value , INTERVAL quantity_expr unit_of_time) DATE or TIMESTAMP Subtracts some unit of time from a date/time input value; See DATE_SUB below See DATE_SUB function below
EXTRACT(field FROM dt) DOUBLE PRECISION Extracts partial date and time values; See EXTRACT section below See EXTRACT function section below
NOW() TIMESTAMP The actual current time at the instant the function is called. Fractional seconds are displayed with a precision of up to 9. Trailing zeros are dropped from fractional seconds. SELECT NOW() FROM DUAL;

Arithmetic operations on timestamps increment or decrement the DAY component of the timestamp date by the value of the interval.

Operations from standard time zones to daylight savings time zones, and vice versa, are adjusted for a 24 hour period, with an apparent result of a 23 or 25 hour day. For example, a session time zone set to 'America/New_York', with timestamp '2013-03-09 12:00' + 1 (add 1 day) will produce a 25 hour timestamp '2013-03-10 13:00', as daylight saving time went into effect at 2013-03-10 02:00.

DATE_ADD and DATE_SUB Functions

The DATE_ADD and DATE_SUB functions return the result of adding or subtracting some INTERVAL of time to/from an input value. The return type is driven by the data type of the input value.

Syntax

DATE_ADD(input_value, INTERVAL quantity_expr unit_of_time) 
DATE_SUB(input_value, INTERVAL quantity_expr unit_of_time)

Parameters

Examples

SELECT DATE_ADD('2014-01-01', INTERVAL 1 DAY) FROM DUAL;
      date_add      
 -------------------
 2014-01-02 00:00:00 

SELECT DATE_ADD('today', INTERVAL 1 WEEK) FROM DUAL;
      date_add      
 -------------------
 2014-01-27 00:00:00
 
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM DUAL;
          date_add         
 --------------------------
 2014-01-20 16:50:24.392728
 
/* SELECT all players who started in the NHL after 2009 and are age 29 or more */
SELECT playerid, firstname||' '||lastname as NAME, position, lastnhl,
      birthyear||'/'||birthmon||'/'||birthday AS BIRTHDAY FROM
( SELECT playerid, firstname, lastname, position,lastnhl,
      birthyear, birthmon, birthday
  FROM hockey.players 
  WHERE firstnhl > 2010
    AND birthday != 0 AND birthyear != 0 AND birthmon != 0
) WHERE DATE_SUB(NOW(), INTERVAL 29 YEAR) > CAST(birthyear||'/'||birthmon||'/'||birthday AS DATE) ;
 PLAYERID       NAME       POSITION  LASTNHL   BIRTHDAY 
 --------- --------------- --------- -------- ----------
 joudran01 Andrew Joudrey      C       2011   1984/7/15 
 kearnbr01 Bracken Kearns      C       2011   1981/5/12 
 petrele01 Lennart Petrell     C       2011   1984/4/13 
 rallogr01 Greg Rallo          C       2011   1981/8/26 
 whitmde01 Derek Whitmore      L       2011   1984/12/17
 
/* adding INTERVAL HOUR to input value of DATE data type returns NULL */
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 5 HOUR) FROM DUAL;
 date_add 
 ---------
  <null>

DATE_DIFF Function

The <interval> refers to the time increments to measure between two dates. For example, to determine the number of whole hours or days between a start date and end date. Valid values are second, minute, hour, day, and month.

The <start_date> refers to the start date of the time period to measure and the <end_date> refers to the end date of that period.

Examples

SELECT DATEDIFF(DAY,'2015-02-01','2015-02-19') FROM DUAL
; 

DAY
---
18

SELECT DATEDIFF(DAY,'2015-02-01 08:30:00.000000','2015-02-19 17:10:00.000000') FROM DUAL
;

DAY
---
18
 

DATE_INTERVAL Function

The <interval> refers to the time increments to measure between two dates. For example, to determine the fractional value of hours or days between a start date and end date. Valid values are second, minute, hour, day, and month.

The <start_date> refers to the start date of the time period to measure and the <end_date> refers to the end date of that period.

Examples

SELECT DATEINTERVAL(DAY,'2015-02-01 08:30:00.000000','2015-02-19 17:10:00.000000') FROM DUAL

; 

DAY
-----------------
18.36111111111111

SELECT DATEINTERVAL(HOUR,'2015-02-01 08:30:00.000000','2015-02-19 17:10:00.000000') FROM DUAL

;



HOUR
-----------------
440.6666666666667
 

EXTRACT Function

The EXTRACT function retrieves subfields (i.e. YEAR, HOUR, etc.) from DATE, TIME, and TIMESTAMP data type values.

Syntax

EXTRACT(field FROM source)

Parameters

Examples

SELECT CURRENT_TIMESTAMP FROM DUAL;
     CURRENT_TIMESTAMP     
 --------------------------
 2014-01-16 15:54:28.350072 
 
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) FROM DUAL;
 HOUR 
 -----
  15  
 
SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL;
 YEAR 
 -----
 2014 
 
SELECT EXTRACT(HOUR FROM CURRENT_DATE) FROM DUAL;
  HOUR 
 ------
 <null>


SELECT EXTRACT(SECOND FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(MINUTE FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(HOUR FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(DAY FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(MONTH FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(QUARTER FROM '2015-01-01 15:54:28.350072'), 
    EXTRACT(YEAR FROM '2015-01-01 15:54:28.350072') FROM DUAL;
 SECOND  MINUTE  HOUR  DAY  MONTH  QUARTER  YEAR 
 ------- ------- ----- ---- ------ -------- -----
   28      54     15    1     1       1     2015

Additional Date/Time Functions

These functions are also supported by NuoDB and can be used instead of the EXTRACT function.

Function Return Type Description Example
YEAR( date-expr ) INTEGER returns the year from date-expr SELECT YEAR(CURRENT_DATE) FROM DUAL;
QUARTER(date-expr) INTEGER returns the quarter from date-expr
The value returned is a number in the range 1..4. As expected, 1 means the month is January, February, or March, 2 means the month is April, May, June, and so on.
SELECT QUARTER(CURRENT_DATE) FROM DUAL;
MONTH( date-expr ) INTEGER returns month from date-expr SELECT MONTH(CURRENT_DATE) FROM DUAL;
DAY( date-expr ) INTEGER returns day from date-expr SELECT DAY(CURRENT_DATE) FROM DUAL;
HOUR( date-expr ) INTEGER returns hour from date-expr SELECT HOUR(CURRENT_TIME) FROM DUAL;
MINUTE( date-expr ) INTEGER returns minute from date-expr SELECT MINUTE(CURRENT_TIME) FROM DUAL;
SECOND( date-expr ) INTEGER returns second from date-expr SELECT SECOND(CURRENT_TIME) FROM DUAL;
MILLISECOND( date-expr ) INTEGER returns milliseconds from date-expr SELECT MILLISECOND(CURRENT_TIME) FROM DUAL;
ERA( date-expr ) INTEGER returns era from date-expr SELECT ERA(CURRENT_DATE) FROM DUAL;
DAYOFWEEK( date-expr ) INTEGER returns day of week from date-expr
(Sunday=1, Monday=2, etc.)
SELECT DAYOFWEEK(CURRENT_DATE) FROM DUAL;
DAYOFYEAR( date-expr ) INTEGER returns day of year from date-expr SELECT DAYOFYEAR(CURRENT_DATE) FROM DUAL;
WEEKOFMONTH( date-expr ) INTEGER returns week of month from date-expr SELECT WEEKOFMONTH(CURRENT_DATE) FROM DUAL;
WEEKOFYEAR( date-expr ) INTEGER returns week of year from date-expr SELECT WEEKOFYEAR(CURRENT_DATE) FROM DUAL;