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, TIMESTAMP and TIMESTAMP WITHOUT TIME ZONE values. The table below illustrates this functionality. Input may be of any of the following:

  • an expression (i.e., table column) of DATE, TIMESTAMP or TIMESTAMP WITHOUT TIME ZONE data type

  • a Date/Time Function (see below)

  • an integer — valid only as the right-side operator, and returns a data type consistent with the left side operator

Operator Description

+

Adding integer to a date/time literal

select CURRENT_DATE + 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 integer from a date/time literal

select CURRENT_DATE - 7 from dual;
/* Today's date - 7 days */
Arithmetic operations using the + and - operators are supported with Date/Time types but the general recommendation is to use the DATE_ADD, DATE_SUB and DATEDIFF functions to get the same results.

Date/Time Functions

The following table presents date and time value processing functions.

Function Description

CONVERT_TZ (input_datetime, source_timezone, target_timezone)

Converts a datetime in the source time zone to the equivalent datetime in the target time zone.


Return type: TIMESTAMP WITHOUT TIME ZONE
See CONVERT_TZ function section below.

CURRENT DATE

Returns the date of the start of the current transaction.


Return type: DATE

SELECT CURRENT DATE FROM DUAL;

CURRENT_DATE

As with CURRENT DATE, returns the date of the start of the current transaction.


Return type: DATE

SELECT CURRENT_DATE FROM DUAL;

CURRENT TIME

Returns the time of the start of the current transaction.


Return type: TIME

SELECT CURRENT TIME FROM DUAL;

CURRENT_TIME
[( [p] )]

As with CURRENT TIME, returns the time 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–6. The default value is 6 (microseconds).


Return type: TIME

SELECT CURRENT_TIME FROM DUAL;

SELECT CURRENT_TIME() FROM DUAL;

SELECT CURRENT_TIME(3) FROM DUAL;

CURRENT TIMESTAMP

Returns the timestamp of the start of the current transaction. See NOW() below for current timestamp.


Return type: TIMESTAMP

SELECT CURRENT TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
[( [p] )]

As with CURRENT TIMESTAMP, returns the timestamp of the start of the current transaction. Fractional seconds are optional and may be specified by the precision value enclosed in parentheses. See NOW() below for current timestamp. Fractional seconds are displayed with a precision of up to 6. Trailing zeros are dropped from fractional seconds. The default value is 6 (microseconds).


Return type: TIMESTAMP

SELECT CURRENT_TIMESTAMP FROM DUAL;

SELECT CURRENT_TIMESTAMP(3) FROM DUAL;

DATE (string)

Converts a string to a DATE data type. See SQL Date and Time Types for a list of date literals.The parsing of the String literal stops after the first word and the rest of the string value is ignored.


Return type: DATE

SELECT DATE('02-01-2013 01:00') FROM DUAL;

DATE_ADD ( input_value , INTERVAL quantity_expr unit_of_time )

Adds a temporal interval to a date/time input value.


Return type: DATE, TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE or TIME
See DATE_ADD function section below.

DATEDIFF (interval, start_date, end_date)

Returns a BIGINT value representing the time between two dates in units. The return value represents the specified boundaries crossed between the specified start_date and end_date.


Return type: BIGINT
See DATEDIFF function section below.

DATEINTERVAL (interval, start_date, end_date)

Returns a double precision value representing the time between two dates in units. The return value represents the specified boundaries crossed between the specified start_date and end_date including fractional component.


Return type: DOUBLE PRECISION
See DATEINTERVAL function section below.

DATE_SUB(input_value , INTERVAL quantity_expr unit_of_time)

Subtracts temporal interval from a date/time input value.


Return type: DATE, TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE or TIME
See DATE_SUB function section below

EXTRACT(field FROM dt)

Extracts partial date and time values.


Return type: DOUBLE PRECISION
See EXTRACT function section below.

NOW()

The current timestamp at the instant that the function is called.


Return type: TIMESTAMP

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 running on TIMESTAMP type arguments 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.

Operations running on TIMESTAMP WITHOUT TIME ZONE type arguments ignore the time zone daylight saving rules.

An operation using identical TIMESTAMP arguments may return different result when run using time zones with different daylight saving rules. The same operation using TIMESTAMP WITHOUT TIME ZONE arguments will always return the same result, regardless of the time zone.

CONVERT_TZ Function

The CONVERT_TZ function returns the datetime in the target time zone equivalent to the input datetime in the source time zone. The result type is TIMESTAMP WITHOUT TIME ZONE.

Syntax

CONVERT_TZ(input_datetime, source_timezone, target_timezone)

Parameters

input_datetime

The input_datetime argument type is TIMESTAMP WITHOUT TIME ZONE. STRING or any other data type that can be converted to TIMESTAMP WITHOUT TIME ZONE may also be used for the input_datetime argument.

source_timezone

The source_timezone must be a valid time zone ID STRING and represents the time zone of input_datetime.

target_timezone

The target_timezone must be a valid time zone ID STRING and represents the time zone to which input_datetime should be converted.

Examples

SELECT CONVERT_TZ(timestamp without time zone'2010-01-01 10:00:00', 'Europe/Rome', 'UTC') from dual;
    [CONVERT_TZ]
 -------------------
 2010-01-01 09:00:00

 SELECT CONVERT_TZ('2010-01-01 10:00:00', 'UTC', 'Europe/Rome') from dual;
     [CONVERT_TZ]
  -------------------
  2010-01-01 11:00:00

DATE_ADD and DATE_SUB Functions

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

Argument type

Interval type

Return type

DATE

DAY,WEEK,MONTH,QUARTER,YEAR

DATE

DATE

MICROSECOND,MILLISECOND,SECOND,MINUTE,HOUR

NULL

TIMESTAMP

ANY

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

ANY

TIMESTAMP WITHOUT TIME ZONE

TIME

MICROSECOND,MILLISECOND,SECOND,MINUTE,HOUR

TIME

TIME

DAY,WEEK,MONTH,QUARTER,YEAR

NULL

STRING LITERAL

ANY

TIMESTAMP

Syntax

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

Parameters

input_value

A value of DATE, TIME or TIMESTAMP data type to be manipulated. If the value is NULL, the function returns a NULL value

quantity_expr

Some quantity of unit_of_time, treated as an integer, specifying the INTERVAL value to be added to the DATE or TIMESTAMP value. If the quantity_expr has fractions it will be rounded up or down, with .5 or greater rounding up. If the value is NULL, the function returns a NULL value.

unit_of_time

A keyword indicating the time units to be added to or subtracted from the input_value. If the input_value data type cannot be manipulated by the unit_of_time specified, the function returns a NULL value. Valid unit_of_time keywords allowed for each valid input_value data type is as follows:

Unit of Time DATE TIME TIMESTAMP TIMESTAMP WITHOUT TIME ZONE

YEAR

X

X

X

QUARTER

X

X

X

MONTH

X

X

X

WEEK

X

X

X

DAY

X

X

X

HOUR

X

X

X

MINUTE

X

X

X

SECOND

X

X

X

MILLISECOND

X

X

X

MICROSECOND

X

X

X

Examples

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

SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM DUAL;
         [DATE_ADD]
 --------------------------
 2014-01-20 16:50:24.392728

SELECT DATE_SUB('2015-01-01', INTERVAL 1 WEEK) FROM DUAL;
     [DATE_SUB]
 -------------------
 2014-12-25 00:00:00

/* 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>

DATEDIFF 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;
 [DATEDIFF]
 -----------
     18

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

DATEINTERVAL 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 year, quarter, month, week, day, hour, minute, second, and millisecond.

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;
  [DATEINTERVAL]
 -----------------
 18.36111111111111

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

EXTRACT Function

The EXTRACT function retrieves subfields (i.e. YEAR, HOUR, etc.) from DATE, TIME, and TIMESTAMP data type values. If the first argument is not a DATE, TIMESTAMP or TIME type, the argument is implicitly converted to a TIMESTAMP type. An error is returned if the conversion fails.

Syntax

EXTRACT(field FROM source)

Parameters

field

An identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of type double precision.
Valid values include: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, QUARTER, ERA, MILLISECOND, DAYOFWEEK, DAYOFYEAR, WEEKOFMONTH, WEEKOFYEAR

source

The source providing the value to be extracted from. This must be a value expression of type DATE, TIME or TIMESTAMP.

Examples

SELECT CURRENT_TIMESTAMP FROM DUAL;
     CURRENT_TIMESTAMP
 --------------------------
 2014-01-16 15:54:28.350072

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) FROM DUAL;
 EXTRACT
 --------
    15

SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL;
 EXTRACT
 --------
   2014

SELECT EXTRACT(HOUR FROM CURRENT_DATE) FROM DUAL;
 EXTRACT
 --------
  <null>

SELECT EXTRACT(SECOND FROM '2015-01-01 15:54:28.350072') AS SECOND,
    EXTRACT(MINUTE FROM '2015-01-01 15:54:28.350072') AS MINUTE,
    EXTRACT(HOUR FROM '2015-01-01 15:54:28.350072') AS HOUR,
    EXTRACT(DAY FROM '2015-01-01 15:54:28.350072') AS DAY,
    EXTRACT(MONTH FROM '2015-01-01 15:54:28.350072') AS MONTH,
    EXTRACT(QUARTER FROM '2015-01-01 15:54:28.350072') AS QUARTER,
    EXTRACT(YEAR FROM '2015-01-01 15:54:28.350072') AS YEAR 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 Description

YEAR( date-expr )

returns the year from date-expr


Return type: INTEGER

SELECT YEAR(CURRENT_DATE) FROM DUAL;

QUARTER(date-expr)

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.


Return type: INTEGER

SELECT QUARTER(CURRENT_DATE) FROM DUAL;

MONTH( date-expr )

returns month from date-expr


Return type: INTEGER

SELECT MONTH(CURRENT_DATE) FROM DUAL;

DAY( date-expr )

returns day from date-expr


Return type: INTEGER

SELECT DAY(CURRENT_DATE) FROM DUAL;

HOUR( date-expr )

returns hour from date-expr


Return type: INTEGER

SELECT HOUR(CURRENT_TIME) FROM DUAL;

MINUTE( date-expr )

returns minute from date-expr


Return type: INTEGER

SELECT MINUTE(CURRENT_TIME) FROM DUAL;

SECOND( date-expr )

returns second from date-expr


Return type: INTEGER

SELECT SECOND(CURRENT_TIME) FROM DUAL;

MILLISECOND( date-expr )

returns milliseconds from date-expr


Return type: INTEGER

SELECT MILLISECOND(CURRENT_TIME) FROM DUAL;

ERA( date-expr )

returns era from date-expr


Return type: INTEGER

SELECT ERA(CURRENT_DATE) FROM DUAL;

DAYOFWEEK( date-expr )

returns day of week from date-expr
(Sunday=1, Monday=2, etc.)


Return type: INTEGER

SELECT DAYOFWEEK(CURRENT_DATE) FROM DUAL;

DAYOFYEAR( date-expr )

returns day of year from date-expr


Return type: INTEGER

SELECT DAYOFYEAR(CURRENT_DATE) FROM DUAL;

WEEKOFMONTH( date-expr )

returns week of month from date-expr


Return type: INTEGER

SELECT WEEKOFMONTH(CURRENT_DATE) FROM DUAL;

WEEKOFYEAR( date-expr )

returns week of year from date-expr


Return type: INTEGER

SELECT WEEKOFYEAR(CURRENT_DATE) FROM DUAL;