Date/Time Functions and Operators ¶

ADDDATE, DATE_ADD ¶

ADDDATE(date, INTERVAL expr unit)¶

ADDDATE(date, days)

DATE_ADD(date, INTERVAL expr unit)¶

The ADDDATE function performs an addition or subtraction operation on a specific DATE value; ADDDATE and DATE_ADD are used interchangeably. The return value is a DATE or DATETIME type. The DATETIME type is returned in the following cases.

The first argument is a DATETIME or TIMESTAMP type
The first argument is a DATE type and the unit of INTERVAL value specified is less than the unit of day

Therefore, to return value of DATETIME type, you should convert the value of first argument by using the CAST function. Even though the date resulting from the operation exceeds the last day of the month, the function returns a valid DATE value considering the last date of the month.

If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

If the calculated value is between ‘0000-00-00 00:00:00’ and ‘0001-01-01 00:00:00’, a value having 0 for all arguments is returned in DATE or DATETIME type. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property. For more information about JDBC connection URL, please see Configuration Connection.

Parameters:

date – It is a DATE, TIMETIME, or TIMESTAMP expression that represents the start date. If an invalid DATE value such as ‘2006-07-00’ is specified, an error is returned.
expr – It represents the interval value to be added to the start date. If a negative number is specified next to the INTERVAL keyword, the interval value is subtracted from the start date.
unit – It represents the unit of the interval value specified in the expr expression. See the following table to specify the format for the interpretation of the interval value. If the value of expr unit is less than the number requested in the unit, it is specified from the smallest unit. For example, if it is HOUR_SECOND, three values such as ‘HOURS:MINUTES:SECONDS’ are required. In the case, if only two values such as “1:1” are given, it is regarded as ‘MINUTES:SECONDS’.

Return type:

DATE or DATETIME

Format of expr for unit

unit Value	expr Format	Example
MILLISECOND	MILLISECONDS	ADDDATE(SYSDATE, INTERVAL 123 MILLISECOND)
SECOND	SECONDS	ADDDATE(SYSDATE, INTERVAL 123 SECOND)
MINUTE	MINUTES	ADDDATE(SYSDATE, INTERVAL 123 MINUTE)
HOUR	HOURS	ADDDATE(SYSDATE, INTERVAL 123 HOUR)
DAY	DAYS	ADDDATE(SYSDATE, INTERVAL 123 DAY)
WEEK	WEEKS	ADDDATE(SYSDATE, INTERVAL 123 WEEK)
MONTH	MONTHS	ADDDATE(SYSDATE, INTERVAL 12 MONTH)
QUARTER	QUARTERS	ADDDATE(SYSDATE, INTERVAL 12 QUARTER)
YEAR	YEARS	ADDDATE(SYSDATE, INTERVAL 12 YEAR)
SECOND_MILLISECOND	‘SECONDS.MILLISECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12.123’ SECOND_MILLISECOND)
MINUTE_MILLISECOND	‘MINUTES:SECONDS.MILLISECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12:12.123’ MINUTE_MILLISECOND)
MINUTE_SECOND	‘MINUTES:SECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12:12’ MINUTE_SECOND)
HOUR_MILLISECOND	‘HOURS:MINUTES:SECONDS.MILLISECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12:12:12.123’ HOUR_MILLISECOND)
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12:12:12’ HOUR_SECOND)
HOUR_MINUTE	‘HOURS:MINUTES’	ADDDATE(SYSDATE, INTERVAL ‘12:12’ HOUR_MINUTE)
DAY_MILLISECOND	‘DAYS HOURS:MINUTES:SECONDS.MILLISECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12 12:12:12.123’ DAY_MILLISECOND)
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’	ADDDATE(SYSDATE, INTERVAL ‘12 12:12:12’ DAY_SECOND)
DAY_MINUTE	‘DAYS HOURS:MINUTES’	ADDDATE(SYSDATE, INTERVAL ‘12 12:12’ DAY_MINUTE)
DAY_HOUR	‘DAYS HOURS’	ADDDATE(SYSDATE, INTERVAL ‘12 12’ DAY_HOUR)
YEAR_MONTH	‘YEARS-MONTHS’	ADDDATE(SYSDATE, INTERVAL ‘12-13’ YEAR_MONTH)

SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL 24 HOUR), ADDDATE(SYSDATE, 1);

03/30/2010  12:00:00.000 AM 03/31/2010               03/31/2010

--it subtracts days when argument < 0
SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL -24 HOUR), ADDDATE(SYSDATE, -1);

03/30/2010  12:00:00.000 AM 03/29/2010               03/29/2010

--when expr is not fully specified for unit
SELECT SYS_DATETIME, ADDDATE(SYS_DATETIME, INTERVAL '1:20' HOUR_SECOND);

06:18:24.149 PM 06/28/2010     06:19:44.149 PM 06/28/2010

SELECT ADDDATE('0000-00-00', 1 );

ERROR: Conversion error in date format.

SELECT ADDDATE('0001-01-01 00:00:00', -1);

'12:00:00.000 AM 00/00/0000'

ADDTIME ¶

ADDTIME(expr1, expr2)¶

The ADDTIME function adds or subtracts a value of specific time. The first argument is DATE, DATETIME, TIMESTAMP, or TIME type and the second argument is TIME, DATETIME, or TIMESTAMP type. Time should be included in the second argument, and the date of the second argument is ignored. The return type for each argument type is follows:

First Argument Type	Second Argument Type	Return Type	Note
TIME	TIME, DATETIME, TIMESTAMP	TIME	The result value must be equal to or less than 24 hours.
DATE	TIME, DATETIME, TIMESTAMP	DATETIME
DATETIME	TIME, DATETIME, TIMESTAMP	DATETIME
date/time string	TIME, DATETIME, TIMESTAMP or time string	VARCHAR	The result string includes time.

Parameters:

expr1 – DATE, DATETIME, TIME or TIMESTAMP type
expr2 – DATETIME, TIMESTAMP, TIME type or date/time string

SELECT ADDTIME(datetime'2007-12-31 23:59:59', time'1:1:2');

01:01:01.000 AM 01/01/2008

SELECT ADDTIME(time'01:00:00', time'02:00:01');

03:00:01 AM

ADD_MONTHS ¶

ADD_MONTHS(date_argument, month)¶

The ADD_MONTHS function adds a month value to the expression date_argument of DATE type, and it returns a DATE type value. If the day (dd) of the value specified as an argument exists within the month of the result value of the operation, it returns the given day (dd); otherwise returns the last day of the given month (dd). If the result value of the operation exceeds the expression range of the DATE type, it returns an error.

Parameters:

date_argument – Specifies an expression of DATE type. To specify a TIMESTAMP or DATETIME value, an explicit casting to DATE type is required. If the value is NULL, NULL is returned.
month – Specifies the number of the months to be added to the date_argument. Both positive and negative values can be specified. If the given value is not an integer type, conversion to an integer type by an implicit casting (rounding to the first place after the decimal point) is performed. If the value is NULL, NULL is returned.

--it returns DATE type value by adding month to the first argument
SELECT ADD_MONTHS(DATE '2008-12-25', 5), ADD_MONTHS(DATE '2008-12-25', -5);

05/25/2009                         07/25/2008

SELECT ADD_MONTHS(DATE '2008-12-31', 5.5), ADD_MONTHS(DATE '2008-12-31', -5.5);

06/30/2009                           06/30/2008

SELECT ADD_MONTHS(CAST (SYS_DATETIME AS DATE), 5), ADD_MONTHS(CAST (SYS_TIMESTAMP AS DATE), 5);

07/03/2010                                     07/03/2010

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT ADD_MONTHS (datetimeltz'2001-10-11 10:11:12', 1);

11/11/2001

SELECT ADD_MONTHS (datetimetz'2001-10-11 10:11:12 Europe/Paris', 1);

11/11/2001

SELECT ADD_MONTHS (timestampltz'2001-10-11 10:11:12', 1);

11/11/2001

SELECT ADD_MONTHS (timestamptz'2001-10-11 10:11:12 Europe/Paris', 1);

11/11/2001

CURDATE, CURRENT_DATE ¶

CURDATE()¶

CURRENT_DATE()¶

CURRENT_DATE¶

CURDATE (), CURRENT_DATE and CURRENT_DATE () are used interchangeably and they return the current date of session as the DATE type (MM/DD/YYYY or YYYY-MM-DD). The unit is day. When the time zone of the current session is same as that of server, these functions are same as SYS_DATE, SYSDATE. Please refer SYS_DATE, SYSDATE and the following examples to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

If input every argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Return type:: DATE

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date in DATE type

SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;

   CURRENT_DATE    CURRENT_DATE    CURRENT_DATE    SYS_DATE    SYS_DATE
========================================================================
  02/05/2016      02/05/2016      02/05/2016      02/05/2016  02/05/2016

-- it returns the date 60 days added to the current date

SELECT CURDATE()+60;

   CURRENT_DATE +60
===================
  04/05/2016

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that CURDATE() and SYS_DATE returns different results

SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;

   CURRENT_DATE    CURRENT_DATE    CURRENT_DATE    SYS_DATE    SYS_DATE
========================================================================
  02/04/2016      02/04/2016      02/04/2016      02/05/2016  02/05/2016

Warning

As 10.0, CURDATE (), CURRENT_DATE, CURRENT_DATE () are different from SYS_DATE and SYSDATE. They are synonym for 9.x and lower.

CURRENT_DATETIME, NOW ¶

CURRENT_DATETIME()¶

CURRENT_DATETIME¶

NOW()¶

CURRENT_DATETIME, CURRENT_DATETIME () and NOW () are used interchangeably, and they return the current date and time of session in DATETIME type. The unit is millisecond. When the time zone of the current session is same as that of server, these functions are same as SYS_DATETIME, SYSDATETIME. Please also refer SYS_DATETIME, SYSDATETIME to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: DATETIME

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date and time in DATETIME type

SELECT NOW(), SYS_DATETIME;

   CURRENT_DATETIME               SYS_DATETIME
==============================================================
  04:05:09.292 PM 02/05/2016     04:05:09.292 PM 02/05/2016

-- it returns the timestamp value 1 hour added to the current sys_datetime value

SELECT TO_CHAR(SYSDATETIME+3600*1000, 'YYYY-MM-DD HH24:MI');

   to_char( SYS_DATETIME +3600*1000, 'YYYY-MM-DD HH24:MI')
======================
  '2016-02-05 17:05'

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

set time zone 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that NOW() and SYS_DATETIME return different results

SELECT NOW(), SYS_DATETIME;

   CURRENT_DATETIME               SYS_DATETIME
==============================================================
  11:08:57.041 PM 02/04/2016     04:08:57.041 PM 02/05/2016

Warning

As 10.0, CURRENT_DATETIME (), NOW () are different from SYS_DATEIME, SYSDATETIME. They are synonym for 9.x and lower.

CURTIME, CURRENT_TIME ¶

CURTIME()¶

CURRENT_TIME¶

CURRENT_TIME()¶

CURTIME (), CURRENT_TIME and CURRENT_TIME () are used interchangeably and they return the current time of session as TIME type (HH:MI:SS). The unit is second. When the time zone of the current session is same as that of server, these functions are same as SYS_TIME, SYSTIME. Please also refer SYS_TIME, SYSTIME to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: TIME

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current time in TIME type

SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;

   CURRENT_TIME    CURRENT_TIME    CURRENT_TIME    SYS_TIME     SYS_TIME
==========================================================================
  04:22:54 PM     04:22:54 PM     04:22:54 PM     04:22:54 PM  04:22:54 PM

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'AMERICA/LOS_ANGELES';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that CURTIME() and SYS_TIME return different results

SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;

   CURRENT_TIME    CURRENT_TIME    CURRENT_TIME    SYS_TIME     SYS_TIME
==========================================================================
  11:23:16 PM     11:23:16 PM     11:23:16 PM     04:23:16 PM  04:23:16 PM

Warning

As 10.0, CURTIME (), CURRENT_TIME () are different from SYS_TIME, SYSTIME. They are synonym for 9.x and lower.

CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP ¶

CURRENT_TIMESTAMP¶

CURRENT_TIMESTAMP()¶

LOCALTIME¶

LOCALTIME()¶

LOCALTIMESTAMP¶

LOCALTIMESTAMP()¶

CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP () are used interchangeably and they return the current date and time of session as TIMESTAMP type. The unit is second. When the time zone of the current session is same as that of server, these functions are same as SYS_TIMESTAMP, SYSTIMESTAMP. Please also refer SYS_TIMESTAMP, SYSTIMESTAMP to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: TIMESTAMP

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date and time in TIMESTAMP type of session and server timezones.

SELECT LOCALTIME, SYS_TIMESTAMP;

   CURRENT_TIMESTAMP          SYS_TIMESTAMP
======================================================
  04:34:16 PM 02/05/2016     04:34:16 PM 02/05/2016

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that LOCALTIME() and SYS_TIMESTAMP return different results

SELECT LOCALTIME, SYS_TIMESTAMP;

   CURRENT_TIMESTAMP          SYS_TIMESTAMP
======================================================
  11:34:37 PM 02/04/2016     04:34:37 PM 02/05/2016

Warning

As 10.0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP () are different from SYS_TIMESTAMP (), SYSTIMESTAMP. They are synonym for 9.x and lower.

DATE ¶

DATE(date)¶

The DATE function extracts the date part from specified argument, and returns it as ‘MM/DD/YYYY’ format string. Arguments that can be specified are DATE, TIMESTAMP and DATETIME types. The return value is a VARCHAR type.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, string where 0 is specified for year, month, and day is returned.

Parameters:: date – DATE, TIMESTAMP or DATETIME can be specified.
Return type:: STRING

SELECT DATE('2010-02-27 15:10:23');

'02/27/2010'

SELECT DATE(NOW());

'04/01/2010'

SELECT DATE('0000-00-00 00:00:00');

'00/00/0000'

DATEDIFF ¶

DATEDIFF(date1, date2)¶

The DATEDIFF function returns the difference between two arguments as an integer representing the number of days. Arguments that can be specified are DATE, TIMESTAMP and DATETIME types and its return value is only INTEGER type.

If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Parameters:: date1,date2 – Specifies the types that include date (DATE, TIMESTAMP or DATETIME) type or string that represents the value of corresponding type. If invalid string is specified, an error is returned.
Return type:: INT

SELECT DATEDIFF('2010-2-28 23:59:59','2010-03-02');

-2

SELECT DATEDIFF('0000-00-00 00:00:00', '2010-2-28 23:59:59');

ERROR: Conversion error in date format.

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT IF(DATEDIFF('2002-03-03 12:00:00 AM','1990-01-01 11:59:59 PM') = DATEDIFF(timestampltz'2002-03-03 12:00:00 AM',timestampltz'1990-01-01 11:59:59 PM'),'ok','nok');

'ok'

DATE_SUB, SUBDATE ¶

DATE_SUB(date, INTERVAL expr unit)¶

SUBDATE(date, INTERVAL expr unit)¶

SUBDATE(date, days)

The functions DATE_SUB and SUBDATE () are used interchangeably and they perform an addition or subtraction operation on a specific DATE value. The value is returned in DATE or DATETIME type. If the date resulting from the operation exceeds the last day of the month, the function returns a valid DATE value considering the last date of the month.

If every input argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

If the calculated value is between ‘0000-00-00 00:00:00’ and ‘0001-01-01 00:00:00’, a value having 0 for all arguments is returned in DATE or DATETIME type. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see Configuration Connection for details).

param date:

It is a DATE or TIMESTAMP expression that represents the start date. If an invalid DATE value such as ‘2006-07-00’ is specified, NULL is returned.

param expr:

It represents the interval value to be subtracted from the start date. If a negative number is specified next to the INTERVAL keyword, the interval value is added to the start date.

param unit:

It represents the unit of the interval value specified in the exp expression. To check the expr argument for the unit value, see the table of ADDDATE().

rtype:

DATE or DATETIME

SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL 24 HOUR), SUBDATE(SYSDATE, 1);

03/30/2010  12:00:00.000 AM 03/29/2010               03/29/2010

--it adds days when argument < 0
SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL -24 HOUR), SUBDATE(SYSDATE, -1);

03/30/2010  12:00:00.000 AM 03/31/2010               03/31/2010

SELECT SUBDATE('0000-00-00 00:00:00', -50);

ERROR: Conversion error in date format.

SELECT SUBDATE('0001-01-01 00:00:00', 10);

'12:00:00.000 AM 00/00/0000'

DAY, DAYOFMONTH ¶

DAY(date)¶

DAYOFMONTH(date)¶

The function DAY or DAYOFMONTH returns day in the range of 1 to 31 from the specified parameter. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to year, month, and day, 0 is returned as an exception.

Parameters:: date – Date
Return type:: INT

SELECT DAYOFMONTH('2010-09-09');

SELECT DAY('2010-09-09 19:49:29');

SELECT DAYOFMONTH('0000-00-00 00:00:00');

DAYOFWEEK ¶

DAYOFWEEK(date)¶

The DAYOFWEEK function returns a day in the range of 1 to 7 (1: Sunday, 2: Monday, …, 7: Saturday) from the specified parameters. The day index is same as the ODBC standards. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Parameters:: date – Date
Return type:: INT

SELECT DAYOFWEEK('2010-09-09');

SELECT DAYOFWEEK('2010-09-09 19:49:29');

SELECT DAYOFWEEK('0000-00-00');

ERROR: Conversion error in date format.

DAYOFYEAR ¶

DAYOFYEAR(date)¶

The DAYOFYEAR function returns the day of a year in the range of 1 to 366. You can specify the DATE, TIMESTAMP or DATETIME types; the value is returned in INTEGER type.

If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Parameters:: date – Date
Return type:: INT

SELECT DAYOFYEAR('2010-09-09');

SELECT DAYOFYEAR('2010-09-09 19:49:29');

SELECT DAYOFYEAR('0000-00-00');

ERROR: Conversion error in date format.

EXTRACT ¶

EXTRACT(field FROM date-time_argument)¶

The EXTRACT operator extracts the values from date-time_argument and then converts the value type into INTEGER.

0 is not allowed in the input argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, 0 is returned as an exception.

Parameters:

field – Specifies a value to be extracted from date-time expression.
date-time_argument – An expression that returns a value of date-time. This expression must be one of TIME, DATE, TIMESTAMP, or DATETIME types. If the value is NULL, NULL is returned.

Return type:

INT

SELECT EXTRACT(MONTH FROM DATETIME '2008-12-25 10:30:20.123' );

SELECT EXTRACT(HOUR FROM DATETIME '2008-12-25 10:30:20.123' );

SELECT EXTRACT(MILLISECOND FROM DATETIME '2008-12-25 10:30:20.123' );

SELECT EXTRACT(MONTH FROM '0000-00-00 00:00:00');

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT EXTRACT (MONTH FROM datetimetz'10/15/1986 5:45:15.135 am Europe/Bucharest');

SELECT EXTRACT (MONTH FROM datetimeltz'10/15/1986 5:45:15.135 am Europe/Bucharest');

SELECT EXTRACT (MONTH FROM timestampltz'10/15/1986 5:45:15 am Europe/Bucharest');

SELECT EXTRACT (MONTH FROM timestamptz'10/15/1986 5:45:15 am Europe/Bucharest');

FROM_DAYS ¶

FROM_DAYS(N)¶

The FROM_DAYS function returns a date value in DATE type if INTEGER type is inputted as an argument.

It is not recommended to use the FROM_DAYS function for dates prior to the year 1582 because the function does not take dates prior to the introduction of the Gregorian Calendar into account.

If a value in the range of 0 to 3,652,424 can be inputted as an argument. If a value in the range of 0 to 365 is inputted, 0 is returned. 3,652,424, which is the maximum value, means the last day of year 9999.

Parameters:: N – Integer in the range of 0 to 3,652,424
Return type:: DATE

SELECT FROM_DAYS(719528);

01/01/1970

SELECT FROM_DAYS('366');

01/03/0001

SELECT FROM_DAYS(3652424);

12/31/9999

SELECT FROM_DAYS(0);

00/00/0000

FROM_TZ ¶

FROM_TZ(datetime, timezone_string)¶

Converts date/time type without timezone as date/time type with timezone by adding timezone to DATETIME typed value. Input value’s type is DATETIME, and the result value’s type is DATETIMETZ.

Parameters:

datetime – DATETIME
timezone_string – String representing a timezone name or and offset ‘+05:00’, ‘Asia/Seoul’.

Return type:

DATETIMETZ

SELECT FROM_TZ(datetime '10/10/2014 00:00:00 AM', 'Europe/Vienna');

12:00:00.000 AM 10/10/2014 Europe/Vienna CEST

SELECT FROM_TZ(datetime '10/10/2014 23:59:59 PM', '+03:25:25');

11:59:59.000 PM 10/10/2014 +03:25:25

For timezone related description, see Date/Time Types with Timezone.

FROM_UNIXTIME ¶

FROM_UNIXTIME(unix_timestamp[, format])¶

The FROM_UNIXTIME function returns the string of the specified format in VARCHAR type if the argument format is specified; if the argument format is omitted, it returns a value of TIMESTAMP type. Specify the arguement unix_timestamp as an INTEGER type that corresponds to the UNIX timestamp. The returned value is displayed in the current time zone.

It displays the result according to the format that you specified, and the date/time format, format follows the Date/Time Format 2 table of DATE_FORMAT().

The relation is not one of one-to-one correspondence between TIMESTAMP and UNIX timestamp so if you use UNIX_TIMESTAMP() or FROM_UNIXTIME function, partial value could be lost. For details, see UNIX_TIMESTAMP().

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, string where 0 is specified for every date and time value is returned. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see Configuration Connection for details).

Parameters:

unix_timestamp – Positive integer
format – Time format. Follows the date/time format of the DATE_FORMAT().

Return type:

STRING, INT

SELECT FROM_UNIXTIME(1234567890);

01:31:30 AM 02/14/2009

SELECT FROM_UNIXTIME('1000000000');

04:46:40 AM 09/09/2001

SELECT FROM_UNIXTIME(1234567890,'%M %Y %W');

'February 2009 Saturday'

SELECT FROM_UNIXTIME('1234567890','%M %Y %W');

'February 2009 Saturday'

SELECT FROM_UNIXTIME(0);

12:00:00 AM 00/00/0000

HOUR ¶

HOUR(time)¶

The HOUR function extracts the hour from the specified parameter and then returns the value in integer. The type TIME, TIMESTAMP or DATETIME can be specified and a value is returned in the INTEGER type.

Parameters:: time – Time
Return type:: INT

SELECT HOUR('12:34:56');

SELECT HOUR('2010-01-01 12:34:56');

SELECT HOUR(datetime'2010-01-01 12:34:56');

LAST_DAY ¶

LAST_DAY(date_argument)¶

The LAST_DAY function returns the last day of the given month as DATE type.

If every input argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Parameters:: date_argument – Specifies an expression of DATE type. To specify a TIMESTAMP or DATETIME value, explicit casting to DATE is required. If the value is NULL, NULL is returned.
Return type:: DATE

--it returns last day of the month in DATE type
SELECT LAST_DAY(DATE '1980-02-01'), LAST_DAY(DATE '2010-02-01');

02/28/1980                    02/28/2010

--it returns last day of the month when explicitly casted to DATE type
SELECT LAST_DAY(CAST (SYS_TIMESTAMP AS DATE)), LAST_DAY(CAST (SYS_DATETIME AS DATE));

02/28/2010                                 02/28/2010

SELECT LAST_DAY('0000-00-00');

ERROR: Conversion error in date format.

MAKEDATE ¶

MAKEDATE(year, dayofyear)¶

The MAKEDATE function returns a date from the specified parameter. You can specify an INTEGER type corresponding to the day of the year in the range of 1 to 9999 as an argument; the value in the range of 1/1/1 to 12/31/9999 is returned in DATE type. If the day of the year has passed the corresponding year, it will become the next year. For example, MAKEDATE(1999, 366) will return 2000-01-01. However, if you input a value in the range of 0 to 69 as the year, it will be processed as the year 2000-2069, if it is a value in the range of 70 to 99, it will be processed as the year 1970-1999.

If every value specified in year and dayofyear is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Parameters:

year – Year in the range of 1 to 9999
dayofyear – If you input a value in the range of 0 to 99 in the argument, it is handled as an exception; dayofyear must be equal to or less than 3,615,902 and the return value of MAKEDATE(100, 3615902) is 9999/12/31.

Return type:

DATE

SELECT MAKEDATE(2010,277);

10/04/2010

SELECT MAKEDATE(10,277);

10/04/2010

SELECT MAKEDATE(70,277);

10/04/1970

SELECT MAKEDATE(100,3615902);

12/31/9999

SELECT MAKEDATE(9999,365);

12/31/9999

SELECT MAKEDATE(0,0);

ERROR: Conversion error in date format.

MAKETIME ¶

MAKETIME(hour, min, sec)¶

The MAKETIME function returns the hour from specified argument in the AM/PM format. You can specify the INTEGER types corresponding hours, minutes and seconds as arguments; the value is returned in DATETIME.

Parameters:

hour – An integer representing the hours in the range of 0 to 23
min – An integer representing the minutes in the range of 0 to 59
sec – An integer representing the minutes in the range of 0 to 59

Return type:

DATETIME

SELECT MAKETIME(13,34,4);

01:34:04 PM

SELECT MAKETIME('1','34','4');

01:34:04 AM

SELECT MAKETIME(24,0,0);

ERROR: Conversion error in time format.

MINUTE ¶

MINUTE(time)¶

The MINUTE function returns the minutes in the range of 0 to 59 from specified argument. You can specify the TIME , TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

Parameters:: time – Time
Return type:: INT

SELECT MINUTE('12:34:56');

SELECT MINUTE('2010-01-01 12:34:56');

SELECT MINUTE('2010-01-01 12:34:56.7890');

MONTH ¶

MONTH(date)¶

The MONTH function returns the month in the range of 1 to 12 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date, 0 is returned as an exception.

Parameters:: date – Date
Return type:: INT

SELECT MONTH('2010-01-02');

SELECT MONTH('2010-01-02 12:34:56');

SELECT MONTH('2010-01-02 12:34:56.7890');

SELECT MONTH('0000-00-00');

MONTHS_BETWEEN ¶

MONTHS_BETWEEN (date_argument, date_argument)

The MONTHS_BETWEEN function returns the difference between the given DATE value. The return value is DOUBLE type. An integer value is returned if the two dates specified as arguments are identical or are the last day of the given month; otherwise, a value obtained by dividing the day difference by 31 is returned.

Parameters:: date_argument – Specifies an expression of DATE type. TIMESTAMP or DATETIME value also can be used. If the value is NULL, NULL is returned.
Return type:: DOUBLE

--it returns the negative months when the first argument is the previous date
SELECT MONTHS_BETWEEN(DATE '2008-12-31', DATE '2010-6-30');

-1.800000000000000e+001

--it returns integer values when each date is the last date of the month
SELECT MONTHS_BETWEEN(DATE '2010-6-30', DATE '2008-12-31');

1.800000000000000e+001

--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_TIMESTAMP AS DATE), DATE '2008-12-25');

1.332258064516129e+001

--it returns months between two arguments when explicitly casted to DATE type
SELECT MONTHS_BETWEEN(CAST (SYS_DATETIME AS DATE), DATE '2008-12-25');

1.332258064516129e+001

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT MONTHS_BETWEEN(datetimetz'2001-10-11 10:11:12 +02:00', datetimetz'2001-05-11 10:11:12 +03:00');

5.000000000000000e+00

NEW_TIME ¶

NEW_TIME(src_datetime, src_timezone, dst_timezone)¶

Moves a date value from a timezone to the other timezone. The type of src_datetime is DATETIME or TIME, and the return value’s type is the same with the src_datetime's value.

Parameters:

src_datetime – input value of DATETIME or TIME
src_timezone – a region name of a source timezone
dst_timezion – a region name of a target timezone

Return type:

the same type with a src_datetimes type

SELECT NEW_TIME(datetime '10/10/2014 10:10:10 AM', 'Europe/Vienna', 'Europe/Bucharest');

11:10:10.000 AM 10/10/2014

TIME type only accept an offset timezone as an input argument; a region name is not allowed.

SELECT NEW_TIME(time '10:10:10 PM', '+03:00', '+10:00');

05:10:10 AM

To see the timezone related description, see Date/Time Types with Timezone.

QUARTER ¶

QUARTER(date)¶

The QUARTER function returns the quarter in the range of 1 to 4 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

Parameters:: date – Date
Return type:: INT

SELECT QUARTER('2010-05-05');

SELECT QUARTER('2010-05-05 12:34:56');

SELECT QUARTER('2010-05-05 12:34:56.7890');

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT QUARTER('2008-04-01 01:02:03 Asia/Seoul');

SELECT QUARTER(datetimetz'2003-12-31 01:02:03.1234 Europe/Paris');

ROUND ¶

ROUND(date, fmt)¶

This function rounds date to the unit specified by the format string, fmt. It returns a value of DATE type.

Parameters:

date – The value of DATE, TIMESTAMP or DATETIME
fmt – Specifies the format for the truncating unit. If omitted, “dd” is default.

Return type:

DATE

The format and its unit and the return value are as follows:

Format	Unit	Return value
‘yyyy’ or ‘yy’	year	a value rounded to year
‘mm’ or ‘month’	month	a value rounded to month
‘q’	quarter	a value rounded to quarter, one of 1/1, 4/1, 7/1, 10/1
‘day’	week	a value rounded to week, this Sunday of date week or the next Sunday of date week
‘dd’	day	a value rounded to day
‘hh’	hour	a value rounded to hour
‘mi’	minute	a value rounded to minute
‘ss’	second	a value rounded to second

SELECT ROUND(date'2012-10-26', 'yyyy');

01/01/2013

SELECT ROUND(timestamp'2012-10-26 12:10:10', 'mm');

11/01/2012

SELECT ROUND(datetime'2012-12-26 12:10:10', 'dd');

12/27/2012

SELECT ROUND(datetime'2012-12-26 12:10:10', 'day');

12/30/2012

SELECT ROUND(datetime'2012-08-26 12:10:10', 'q');

10/01/2012

SELECT TRUNC(datetime'2012-08-26 12:10:10', 'q');

07/01/2012

SELECT ROUND(datetime'2012-02-28 23:10:00', 'hh');

02/28/2012

SELECT ROUND(datetime'2012-02-28 23:58:59', 'hh');

02/29/2012

SELECT ROUND(datetime'2012-02-28 23:59:59', 'mi');

02/29/2012

SELECT ROUND(datetime'2012-02-28 23:59:59.500', 'ss');

02/29/2012

In order to truncate date instead of rounding, please see TRUNC(date, fmt).

SEC_TO_TIME ¶

SEC_TO_TIME(second)¶

The SEC_TO_TIME function returns the time including hours, minutes and seconds from specified argument. You can specify the INTEGER type in the range of 0 to 86,399; the value is returned in TIME type.

Parameters:: second – Seconds in the range of 0 to 86,399
Return type:: TIME

SELECT SEC_TO_TIME(82800);

  sec_to_time(82800)
=====================
  11:00:00 PM

SELECT SEC_TO_TIME('82800.3');

  sec_to_time('82800.3')
=========================
  11:00:00 PM

SELECT SEC_TO_TIME(86399);

  sec_to_time(86399)
=====================
  11:59:59 PM

SECOND ¶

SECOND(time)¶

The SECOND function returns the seconds in the range of 0 to 59 from specified argument. You can specify the TIME, TIMESTAMP or DATETIME; the value is returned in INTEGER type.

Parameters:: time – Time
Return type:: INT

SELECT SECOND('12:34:56');

   second('12:34:56')
=====================
                   56

SELECT SECOND('2010-01-01 12:34:56');

   second('2010-01-01 12:34:56')
================================
                              56

SELECT SECOND('2010-01-01 12:34:56.7890');

   second('2010-01-01 12:34:56.7890')
=====================================
                                   56

SYS_DATE, SYSDATE ¶

SYS_DATE¶

SYSDATE¶

SYS_DATE and SYSDATE are used interchangeably and they return the current date of server as the DATE type (MM/DD/YYYY or YYYY-MM-DD). The unit is day. When the time zone of the current session is same as that of server, these functions are same as CURDATE(), CURRENT_DATE() and CURRENT_DATE. Please also refer CURDATE(), CURRENT_DATE() to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

If input every argument value of year, month, and day is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

Return type:: DATE

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date in DATE type

SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;

   CURRENT_DATE    CURRENT_DATE    CURRENT_DATE    SYS_DATE    SYS_DATE
========================================================================
  02/05/2016      02/05/2016      02/05/2016      02/05/2016  02/05/2016

-- it returns the date 60 days added to the current date

SELECT CURDATE()+60;

   CURRENT_DATE +60
===================
  04/05/2016

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that CURDATE() and SYS_DATE returns different results

SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE, SYS_DATE, SYSDATE;

   CURRENT_DATE    CURRENT_DATE    CURRENT_DATE    SYS_DATE    SYS_DATE
========================================================================
  02/04/2016      02/04/2016      02/04/2016      02/05/2016  02/05/2016

Warning

As 10.0, SYS_DATE and SYSDATE are different from CURDATE (), CURRENT_DATE, CURRENT_DATE (). They are synonym for 9.x and lower.

SYS_DATETIME, SYSDATETIME ¶

SYS_DATETIME¶

SYSDATETIME¶

SYS_DATETIME and SYSDATETIME are used interchangeably, and they return the current date and time of server in DATETIME type. The unit is millisecond. When the time zone of the current session is same as that of server, these functions are same as CURRENT_DATETIME(), CURRENT_DATETIME, NOW(). Please also refer CURRENT_DATETIME(), NOW() to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: DATETIME

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date and time in DATETIME type

SELECT NOW(), SYS_DATETIME;

   CURRENT_DATETIME               SYS_DATETIME
==============================================================
  04:05:09.292 PM 02/05/2016     04:05:09.292 PM 02/05/2016

-- it returns the timestamp value 1 hour added to the current sys_datetime value

SELECT TO_CHAR(SYSDATETIME+3600*1000, 'YYYY-MM-DD HH24:MI');

   to_char( SYS_DATETIME +3600*1000, 'YYYY-MM-DD HH24:MI')
======================
  '2016-02-05 17:05'

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that NOW() and SYS_DATETIME return different results

SELECT NOW(), SYS_DATETIME;

   CURRENT_DATETIME               SYS_DATETIME
==============================================================
  11:08:57.041 PM 02/04/2016     04:08:57.041 PM 02/05/2016

Warning

As 10.0, SYS_DATEIME, SYSDATETIME are different from CURRENT_DATETIME (), NOW (). They are synonym for 9.x and lower.

SYS_TIME, SYSTIME ¶

SYS_TIME¶

SYSTIME¶

SYS_TIME and SYSTIME are used interchangeably and they return the current time of server as TIME type (HH:MI:SS). The unit is second. When the time zone of the current session is same as that of server, these functions are same as CURTIME(), CURRENT_TIME, CURRENT_TIME(). Please also refer CURTIME(), CURRENT_TIME() to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: TIME

select dbtimezone(), sessiontimezone();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current time in TIME type

SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;

   CURRENT_TIME    CURRENT_TIME    CURRENT_TIME    SYS_TIME     SYS_TIME
==========================================================================
  04:22:54 PM     04:22:54 PM     04:22:54 PM     04:22:54 PM  04:22:54 PM

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

select dbtimezone(), sessiontimezone();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that CURTIME() and SYS_TIME return different results

SELECT CURTIME(), CURRENT_TIME(), CURRENT_TIME, SYS_TIME, SYSTIME;

   CURRENT_TIME    CURRENT_TIME    CURRENT_TIME    SYS_TIME     SYS_TIME
==========================================================================
  11:23:16 PM     11:23:16 PM     11:23:16 PM     04:23:16 PM  04:23:16 PM

Warning

As 10.0, SYS_TIME, SYSTIME are different from CURTIME (), CURRENT_TIME (). They are synonym for 9.x and lower.

SYS_TIMESTAMP, SYSTIMESTAMP ¶

SYS_TIMESTAMP¶

SYSTIMESTAMP¶

SYS_TIMESTAMP and SYSTIMESTAMP are used interchangeably and they return the current date and time of server as TIMESTAMP type. The unit is second. When the time zone of the current session is same as that of server, these functions are same as CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP(). Please also refer CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP() to find a difference and DBTIMEZONE(), SESSIONTIMEZONE() for details of the functions.

Return type:: TIMESTAMP

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'Asia/Seoul'

-- it returns the current date and time in TIMESTAMP type of session and server timezones.

SELECT LOCALTIME, SYS_TIMESTAMP;

   CURRENT_TIMESTAMP          SYS_TIMESTAMP
======================================================
  04:34:16 PM 02/05/2016     04:34:16 PM 02/05/2016

-- change session time from 'Asia/Seoul' to 'America/Los_Angeles'

SET TIME ZONE 'America/Los_Angeles';

SELECT DBTIMEZONE(), SESSIONTIMEZONE();

  dbtimezone            sessiontimezone
============================================
  'Asia/Seoul'          'America/Los_Angeles'

-- Note that LOCALTIME() and SYS_TIMESTAMP return different results

SELECT LOCALTIME, SYS_TIMESTAMP;

   CURRENT_TIMESTAMP          SYS_TIMESTAMP
======================================================
  11:34:37 PM 02/04/2016     04:34:37 PM 02/05/2016

Warning

As 10.0, SYS_TIMESTAMP (), SYSTIMESTAMP are different from CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), LOCALTIME, LOCALTIME (), LOCALTIMESTAMP and LOCALTIMESTAMP (). They are synonym for 9.x and lower.

TIME ¶

TIME(time)¶

The TIME function extracts the time part from specified argument and returns the VARCHAR type string in the ‘HH:MI:SS’ format. You can specify the TIME, TIMESTAMP and DATETIME types.

Parameters:: time – Time
Return type:: STRING

SELECT TIME('12:34:56');

   time('12:34:56')
======================
  '12:34:56'

SELECT TIME('2010-01-01 12:34:56');

   time('2010-01-01 12:34:56')
======================
  '12:34:56'

SELECT TIME(datetime'2010-01-01 12:34:56');

   time(datetime '2010-01-01 12:34:56')
======================
  '12:34:56'

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT TIME(datetimetz'1996-02-03 02:03:04 AM America/Lima PET');

'02:03:04'

SELECT TIME(datetimeltz'1996-02-03 02:03:04 AM America/Lima PET');

'16:03:04'

SELECT TIME(datetimeltz'2000-12-31 17:34:23.1234 -05:00');

'07:34:23.123'

SELECT TIME(datetimetz'2000-12-31 17:34:23.1234 -05:00');

'17:34:23.123'

TIME_TO_SEC ¶

TIME_TO_SEC(time)¶

The TIME_TO_SEC function returns the seconds in the range of 0 to 86,399 from specified argument. You can specify the TIME, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

Parameters:: time – Time
Return type:: INT

SELECT TIME_TO_SEC('23:00:00');

SELECT TIME_TO_SEC('2010-10-04 23:00:00');

SELECT TIME_TO_SEC('2010-10-04 23:00:00.1234');

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT TIME_TO_SEC(datetimeltz'1996-02-03 02:03:04 AM America/Lima PET');

57784

SELECT TIME_TO_SEC(datetimetz'1996-02-03 02:03:04 AM America/Lima PET');

7384

TIMEDIFF ¶

TIMEDIFF(expr1, expr2)¶

The TIMEDIFF function returns the time difference between the two specified time arguments. You can enter a date/time type, the TIME, DATE, TIMESTAMP or DATETIME type and the data types of the two arguments must be identical. The TIME will be returned and the time difference between the two arguments must be in the range of 00:00:00 -23:59:59. If it exceeds the range, an error will be returned.

Parameters:: expr2 (expr1,) – Time. The data types of the two arguments must be identical.
Return type:: TIME

SELECT TIMEDIFF(time '17:18:19', time '12:05:52');

05:12:27 AM

SELECT TIMEDIFF('17:18:19','12:05:52');

05:12:27 AM

SELECT TIMEDIFF('2010-01-01 06:53:45', '2010-01-01 03:04:05');

03:49:40 AM

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT TIMEDIFF (datetimeltz'2013-10-28 03:11:12 AM Asia/Seoul', datetimeltz'2013-10-27 04:11:12 AM Asia/Seoul');

11:00:00 PM

TIMESTAMP ¶

TIMESTAMP(date[, time])¶

The TIMESTAMP function converts a DATE or TIMESTAMP type expression to DATETIME type.

If the DATE format string (’YYYY-MM-DD’ or ‘MM/DD/YYYY’) or TIMESTAMP format string (’YYYY-MM-DD HH:MI:SS’ or ‘HH:MI:SS MM/DD/ YYYY’) is specified as the first argument, the function returns it as DATETIME.

If the TIME format string (’HH:MI:SS.*FF*’) is specified as the second, the function adds it to the first argument and returns the result as a DATETIME type. If the second argument is not specified, 12:00:00.000 AM is specified by default.

Parameters:

date – The format strings can be specified as follows: ‘YYYY-MM-DD’, ‘MM/DD/YYYY’, ‘YYYY-MM-DD HH:MI:SS.*FF*’, ‘HH:MI:SS.*FF* MM/DD/YYYY’.
time – The format string can be specified as follows: ‘HH:MI:SS*[.*FF]’.

Return type:

DATETIME

SELECT TIMESTAMP('2009-12-31'), TIMESTAMP('2009-12-31','12:00:00');

12:00:00.000 AM 12/31/2009     12:00:00.000 PM 12/31/2009

SELECT TIMESTAMP('2010-12-31 12:00:00','12:00:00');

12:00:00.000 AM 01/01/2011

SELECT TIMESTAMP('13:10:30 12/25/2008');

01:10:30.000 PM 12/25/2008

The following are examples of using timezone type values. For timezone related description, see Date/Time Types with Timezone.

SELECT TIMESTAMP(datetimetz'2010-12-31 12:00:00 America/New_York', '03:00');

03:00:00.000 PM 12/31/2010

SELECT TIMESTAMP(datetimeltz'2010-12-31 12:00:00 America/New_York', '03:00');

05:00:00.000 AM 01/01/2011

TO_DAYS ¶

TO_DAYS(date)¶

The TO_DAYS function returns the number of days after year 0 in the rage of 366 to 3652424 from specified argument. You can specify DATE type; the value is returned in INTEGER type.

It is not recommended to use the TO_DAYS function for dates prior to the year 1582, as the function does not take dates prior to the introduction of the Gregorian Calendar into account.

Parameters:: date – Date
Return type:: INT

SELECT TO_DAYS('2010-10-04');

   to_days('2010-10-04')
========================
                  734414

SELECT TO_DAYS('2010-10-04 12:34:56');

   to_days('2010-10-04 12:34:56')
================================
                          734414

SELECT TO_DAYS('2010-10-04 12:34:56.7890');

   to_days('2010-10-04 12:34:56.7890')
======================================
                                734414

SELECT TO_DAYS('1-1-1');

   to_days('1-1-1')
===================
                366

SELECT TO_DAYS('9999-12-31');

   to_days('9999-12-31')
========================
                 3652424

TRUNC ¶

TRUNC(date[, fmt])¶

This function truncates date to the unit specified by the format string, fmt. It returns a value of DATE type.

Parameters:

date – The value of DATE, TIMESTAMP or DATETIME
fmt – Specifies the format for the truncating unit. If omitted, “dd” is default.

Return type:

DATE

The format and its unit and the return value are as follows:

Format	Unit	Return value
‘yyyy’ or ‘yy’	year	the same year with Jan. 1st
‘mm’ or ‘month’	month	the same month with 1st
‘q’	quarter	the same quarter with one of Jan. 1st, Apr. 1st, Jul. 1st, Oct. 1st
‘day’	week	Sunday of the same week(starting date of the week including date)
‘dd’	day	the same date with date

SELECT TRUNC(date'2012-12-26', 'yyyy');

01/01/2012

SELECT TRUNC(timestamp'2012-12-26 12:10:10', 'mm');

12/01/2012

SELECT TRUNC(datetime'2012-12-26 12:10:10', 'q');

10/01/2012

SELECT TRUNC(datetime'2012-12-26 12:10:10', 'dd');

12/26/2012

// It returns the date of Sunday of the week which includes date'2012-12-26'
SELECT TRUNC(datetime'2012-12-26 12:10:10', 'day');

12/23/2012

In order to round date instead of truncation, please see ROUND(date, fmt).

TZ_OFFSET ¶

TZ_OFFSET(timezone_string)¶

This returns a timezone offset from a timezone offset or timezone region name (e.g. ‘-05:00’, or ‘Europe/Vienna’).

Parameters:: timezone_string – timezone offset of timezone region name.
Return type:: STRING

SELECT TZ_OFFSET('+05:00');

'+05:00'

SELECT TZ_OFFSET('Asia/Seoul');

'+09:00'

For timezone related description, see Date/Time Types with Timezone.

UNIX_TIMESTAMP ¶

UNIX_TIMESTAMP([date])¶

The argument of the UNIX_TIMESTAMP function can be omitted. If it is omitted, the function returns the interval between ‘1970-01-01 00:00:00’ UTC and the current system date/time in seconds as INTEGER type. If the date argument is specified, the function returns the interval between ‘1970-01-01 00:00:00’ UTC and the specified date/time in seconds.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is given in every argument value corresponding to date and time, 0 is returned as an exception.

Argument of DATETIME type is considered in session timezone.

Parameters:: date – DATE type, TIMESTAMP type, TIMESTAMPTZ type, TIMESTAMPLTZ type, DATETIME type, DATETIMETZ type, DATETIMELTZ type, DATE format string (’YYYY-MM-DD’ or ‘MM/DD/YYYY’), TIMESTAMP format string (’YYYY-MM-DD HH:MI:SS’, ‘HH:MI:SS MM/DD/YYYY’) or ‘YYYYMMDD’ format string can be specified.
Return type:: INT

SELECT UNIX_TIMESTAMP('1970-01-02'), UNIX_TIMESTAMP();

   unix_timestamp('1970-01-02')   unix_timestamp()
==================================================
                          54000         1270196737

SELECT UNIX_TIMESTAMP ('0000-00-00 00:00:00');

   unix_timestamp('0000-00-00 00:00:00')
========================================
                                       0

 -- when used without argument, it returns the exact value at the moment of execution of each occurence
 SELECT  UNIX_TIMESTAMP(), SLEEP(1), UNIX_TIMESTAMP();

   unix_timestamp()     sleep(1)   unix_timestamp()
===================================================
         1454661297            0         1454661298

UTC_DATE ¶

UTC_DATE()¶

The UTC_DATE function returns the UTC date in ‘YYYY-MM-DD’ format.

Return type:: STRING

SELECT UTC_DATE();

  utc_date()
==============
  01/12/2011

UTC_TIME ¶

UTC_TIME()¶

The UTC_TIME function returns the UTC time in ‘HH:MI:SS’ format.

Return type:: STRING

SELECT UTC_TIME();

  utc_time()
==============
  10:35:52 AM

WEEK ¶

WEEK(date[, mode])¶

The WEEK function returns the week in the range of 0 to 53 from specified argument. You can specify the DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

Parameters:

date – Date
mode – Value in the range of 0 to 7

Return type:

INT

You can omit the second argument, mode and must input a value in the range of 0 to 7. You can set that a week starts from Sunday or Monday and the range of the return value is from 0 to 53 or 1 to 53 with this value. If you omit the mode, the system parameter, default_week_format value(default: 0) will be used. The mode value means as follows:

mode	Start Day of the Week	Range	The First Week of the Year
0	Sunday	0~53	The first week that Sunday is included in the year
1	Monday	0~53	The first week that more than three days are included in the year
2	Sunday	1~53	The first week in the year that includes a Sunday
3	Monday	1~53	The first week in the year that includes more than three days
4	Sunday	0~53	The first week in the year that includes more than three days
5	Monday	0~53	The first week in the year that includes Monday
6	Sunday	1~53	The first week in the year that includes more than three days
7	Monday	1~53	The first week in the year that includes Monday

If the mode value is one of 0, 1, 4 or 5, and the date corresponds to the last week of the previous year, the WEEK function will return 0. The purpose is to see what nth of the year the week is so it returns 0 for the 52th week of the year 1999.

SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

   year('2000-01-01')   week('2000-01-01', 0)
=============================================
                2000                       0

To see what n-th the week is based on the year including the start day of the week, use 0, 2, 5 or 7 as the mode value.

SELECT WEEK('2000-01-01',2);

    week('2000-01-01', 2)
========================
                      52

SELECT WEEK('2010-04-05');

   week('2010-04-05', 0)
========================
                      14

SELECT WEEK('2010-04-05 12:34:56',2);

   week('2010-04-05 12:34:56',2)
===============================
                              14

SELECT WEEK('2010-04-05 12:34:56.7890',4);

   week('2010-04-05 12:34:56.7890',4)
====================================
                                  14

WEEKDAY ¶

WEEKDAY(date)¶

The WEEKDAY function returns the day of week in the range of 0 to 6 (0: Monday, 1: Tuesday, …, 6: Sunday) from the specified parameter. You can specify DATE, TIMESTAMP, DATETIME types as parameters and an INTEGER type will be returned.

Parameters:: date – Date
Return type:: INT

SELECT WEEKDAY('2010-09-09');

   weekday('2010-09-09')
========================
                       3

SELECT WEEKDAY('2010-09-09 13:16:00');

   weekday('2010-09-09 13:16:00')
=================================
                                3

YEAR ¶

YEAR(date)¶

The YEAR function returns the year in the range of 1 to 9,999 from the specified parameter. You can specify DATE, TIMESTAMP or DATETIME type; the value is returned in INTEGER type.

Parameters:: date – Date
Return type:: INT

SELECT YEAR('2010-10-04');

   year('2010-10-04')
=====================
                 2010

SELECT YEAR('2010-10-04 12:34:56');

   year('2010-10-04 12:34:56')
==============================
                          2010

SELECT YEAR('2010-10-04 12:34:56.7890');

   year('2010-10-04 12:34:56.7890')
===================================
                               2010