Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Arithmetic Operations and Type Casting of DATE/TIME Data Types

Description

If all operands are date/time type, only a subtraction operation is allowed and its return value is BIGINT. Note that the unit of the operation differs depending on the types of the operands. Both addition and subtraction operations are allowed in case of date/time and integer types In this case, operation units and return values are date/time data type.

The following table shows operations allowed for each operand type, and their result types.

Allowable Operation and Result Data Type by Operand Type

 

TIME
(in seconds)

DATE
(in day)

TIMESTAMP
(in seconds)

DATETIME
(in milliseconds)

INT

TIME

A subtraction is allowed.
BIGINT

X

X

X

An addition and a subtraction are allowed.
TIME

DATE

X

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

An addition and a subtraction are allowed.
DATE

TIMESTAMP

X

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

An addition and a subtraction are allowed.
TIMESTAMP

DATETIME

X

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

A subtraction is allowed.
BIGINT

An addition and a subtraction are allowed.
DATETIME

INT

An addition and a subtraction are allowed.
TIME

An addition and a subtraction are allowed.
DATE

An addition and a subtraction are allowed.
TIMESTAMP

An addition and a subtraction are allowed.
DATETIME

All operations are allowed.

Remark

If any of the date/time arguments contains NULLNULL is returned.

Example

-- initial systimestamp value

SELECT SYSDATETIME;

  SYSDATETIME

===============================

  07:09:52.115 PM 01/14/2010

 

-- time type + 10(seconds) returns time type

SELECT (CAST (SYSDATETIME AS TIME) + 10);

 ( cast( SYS_DATETIME  as time)+10)

====================================

  07:10:02 PM

 

-- date type + 10 (days) returns date type

SELECT (CAST (SYSDATETIME AS DATE) + 10);

 ( cast( SYS_DATETIME  as date)+10)

====================================

  01/24/2010

 

-- timestamp type + 10(seconds) returns timestamp type

SELECT (CAST (SYSDATETIME AS TIMESTAMP) + 10);

 ( cast( SYS_DATETIME  as timestamp)+10)

=========================================

  07:10:02 PM 01/14/2010

 

-- systimestamp type + 10(milliseconds) returns systimestamp type

SELECT (SYSDATETIME  + 10);

 ( SYS_DATETIME +10)

===============================

  07:09:52.125 PM 01/14/2010

 

SELECT DATETIME '09/01/2009 03:30:30.001 pm'- TIMESTAMP '08/31/2009 03:30:30 pm';

 datetime '09/01/2009 03:30:30.001 pm'-timestamp '08/31/2009 03:30:30 pm'

=======================================

  86400001

 

SELECT TIMESTAMP '09/01/2009 03:30:30 pm'- TIMESTAMP '08/31/2009 03:30:30 pm';

 timestamp '09/01/2009 03:30:30 pm'-timestamp '08/31/2009 03:30:30 pm'

=======================================

  86400