Hitachi

Hitachi Advanced Database SQL Reference

8.9.9 TIMESTAMPDIFF

Return the difference between the start date and time and the end date and time.

Note

There is no functional difference between the scalar function TIMESTAMPDIFF and the scalar function DATEDIFF.

Organization of this subsection

(1) Specification format

scalar-function-TIMESTAMPDIFF::= TIMESTAMPDIFF(datetime-unit,start-datetime,end-datetime)
 
  datetime-unit::= {YEAR|QUARTER|MONTH|WEEK|DAY|DAYOFYEAR|HOUR|MINUTE
                 |SECOND|MILLISECOND|MICROSECOND|NANOSECOND|PICOSECOND}
  start-datetime::= value-expression
  end-datetime::= value-expression

To Page Top

(2) Explanation of specification format

datetime-unit:

Specify the unit when determining the difference between the start date and time and the end date and time. Specify one of the values listed below.

  • YEAR

    Specify if determining the difference between the start date and time and the end date and time using year unit.

    (Example)

    TIMESTAMPDIFF(YEAR,'2018-05-05','2020-07-10') → 2

    TIMESTAMPDIFF(YEAR,'2020-05-05','2020-07-10') → 0

    TIMESTAMPDIFF(YEAR,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • QUARTER

    Specify if determining the difference between the start date and time and the end date and time using quarter units. The calculation is based on January 1 and is divided into three-month increments.

    First quarter: 01/ 01 to 03/31

    Second quarter: 04/01 to 06/30

    Third quarter: 07/01 to 09/30

    Fourth quarter: 10/01 to 12/31

    (Example)

    TIMESTAMPDIFF(QUARTER,'2020-01-05','2020-07-10') → 2

    TIMESTAMPDIFF(QUARTER,'2020-01-05','2020-03-10') → 0

    TIMESTAMPDIFF(QUARTER,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • MONTH

    Specify if determining the difference between the start date and time and the end date and time using month units.

    (Example)

    TIMESTAMPDIFF(MONTH,'2020-01-05','2020-07-10') → 6

    TIMESTAMPDIFF(MONTH,'2020-06-05','2020-06-10') → 0

    TIMESTAMPDIFF(MONTH,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • WEEK

    Specify if determining the difference between the start date and time and the end date and time using week units. The first day of the week is calculated as Sunday.

    (Example)

    TIMESTAMPDIFF(WEEK,'2020-07-03','2020-07-06') → 1

    Since July 5, 2020 is a Sunday and the week has changed, 1 is returned.

    TIMESTAMPDIFF(WEEK,'2019-12-30','2020-01-01') → 0

    Since December 30, 2019 is a Monday and the week has not changed, 0 is returned.

  • DAY

    Specify if determining the difference between the start date and time and the end date and time using day units.

    (Example)

    TIMESTAMPDIFF(DAY,'2020-07-05','2020-07-10') → 5

    TIMESTAMPDIFF(DAY,'2020-07-05 08:02:25','2020-07-05 17:55:18') → 0

    TIMESTAMPDIFF(DAY,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • DAYOFYEAR

    Specify if determining the difference between the start date and time and the end date and time using ordinal date units. The same result will be returned as when specifying DAY.

    (Example)

    TIMESTAMPDIFF(DAYOFYEAR,'2020-07-05','2020-07-10') → 5

    TIMESTAMPDIFF(DAYOFYEAR,'2020-07-05 08:02:25','2020-07-05 17:55:18') → 0

    TIMESTAMPDIFF(DAYOFYEAR,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • HOUR

    Specify if determining the difference between the start date and time and the end date and time using hour units.

    (Example)

    TIMESTAMPDIFF(HOUR,'2020-07-10 08:02:25','2020-07-10 11:37:55') → 3

    TIMESTAMPDIFF(HOUR,'2020-07-10 08:02:25','2020-07-10 08:45:15') → 0

    TIMESTAMPDIFF(HOUR,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • MINUTE

    Specify if determining the difference between the start date and time and the end date and time using minute units.

    (Example)

    TIMESTAMPDIFF(MINUTE,'2020-07-10 08:02:25','2020-07-10 08:07:25') → 5

    TIMESTAMPDIFF(MINUTE,'2020-07-10 08:02:25','2020-07-10 08:02:32') → 0

    TIMESTAMPDIFF(MINUTE,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • SECOND

    Specify if determining the difference between the start date and time and the end date and time using second units.

    (Example)

    TIMESTAMPDIFF(SECOND,'2020-07-10 08:02:25','2020-07-10 08:02:33') → 8

    TIMESTAMPDIFF(SECOND,'2019-12-31 23:59:59','2020-01-01 00:00:00') → 1

  • MILLISECOND

    Specify if determining the difference between the start date and time and the end date and time using millisecond (1/1,000 of a second) units.

    (Example)

    TIMESTAMPDIFF(MILLISECOND,'08:02:25.000','08:02:25.003') → 3

    TIMESTAMPDIFF(MILLISECOND,'08:02:24.000','08:02:25.001') → 1001

    TIMESTAMPDIFF(MILLISECOND,'08:02:25.000000','08:02:25.003111') → 3

  • MICROSECOND

    Specify if determining the difference between the start date and time and the end date and time using microsecond (1/1,000,000 of a second) units.

    (Example)

    TIMESTAMPDIFF(MICROSECOND,'08:02:25.000000','08:02:25.000012') → 12

  • NANOSECOND

    Specify if determining the difference between the start date and time and the end date and time using nanosecond (1/1,000,000,000 of a second) units.

    (Example)

    TIMESTAMPDIFF(NANOSECOND,'08:02:25.000000000','08:02:25.000000123') → 123

  • PICOSECOND

    Specify if determining the difference between the start date and time and the end date and time using picosecond (1/1,000,000,000,000 of a second) units.

    (Example)

    TIMESTAMPDIFF(PICOSECOND,'08:02:25.000000000000','08:02:25.000000000003') → 3

start-datetime:

Specifies the start date and time.

Note the following rules:

  • Specify the start date and time in the form of a value expression. For details about value expressions, see 7.21 Value expression.

  • The data type for the start date and time should be DATE, TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

  • You cannot specify a dynamic parameter by itself for the start date and time.

end-datetime:

Specifies the end date and time.

Note the following rules:

  • Specify the end date and time in the form of a value expression. For details about value expressions, see 7.21 Value expression.

  • The data type for the end date and time should be DATE, TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

  • You cannot specify a dynamic parameter by itself for the end date and time.

To Page Top

(3) Rules

  1. For the execution result, the value obtained by subtracting start-datetime from end-datetime is returned. If end-datetime is earlier than start-datetime, a negative value is returned.

  2. The difference is determined by setting a default value for the missing element of the datetime data specified for the start-datetime or end-datetime. A 00:00:00 is assumed for the time element of date data that is missing a time element. When the fractional seconds are missing, all the missing digits are assumed to be 0.

    For example, when the hour, minutes, and seconds are missing in the datetime data, for example when start-datetime is DATE type and end-datetime is TIMESTAMP type, the hour, minutes, and seconds are assumed to be 00:00:00. When the fractional seconds are missing, all the missing digits are assumed to be 0.

    Example: TIMESTAMPDIFF(SECOND,'2020-07-10','2020-07-10 00:00:07') → 7

    In the example above, start-datetime is assumed to be '2020-07-10 00:00:00'.

  3. If you specify a DATE type, TIMESTAMP type, predefined input representation of a date, or a predefined input representation of a time stamp for start-datetime, you must also specify a DATE type, TIMESTAMP type, predefined input representation of a date, or a predefined input representation of a time stamp for end-datetime.

  4. If you specify a TIME type or a predefined input representation of a time for start-datetime, you must also specify a TIME type or a predefined input representation of a time for end-datetime.

  5. If you specify a TIME type or a predefined input representation of a time for start-datetime and end-datetime, and YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, or WEEK for datetime-unit, the value of the execution result will be 0.

  6. The data type of the execution result is the BIGINT type. An error results if the execution result exceeds the range that can be represented by the BIGINT type. For the range that can be represented by the BIGINT type, see (1) Numeric data in 6.2.1 List of data types.

    Note that if the integer data type format is a legacy format, the data type of the execution result will be an INTEGER type. An error results if the execution result exceeds the range that can be represented by the INTEGER type. For the range that can be represented by the INTEGER type, see (1) Numeric data in 6.2.1 List of data types.

  7. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

  8. If start-datetime or end-datetime has a null value, the execution result will be a null value.

  9. The following example illustrates the value result of executing the scalar function TIMESTAMPDIFF is 1. In this example, the difference between the start-datetime and the end-datetime is 1 second.

    Date and time unit specification

    Start-datetime specification

    End-datetime specification

    Execution result

    YEAR

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    QUARTER

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    MONTH

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    WEEK

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1#

    DAY

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    DAYOFYEAR

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    HOUR

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    MINUTE

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1

    SECOND

    '2011-12-31 23:59:59'

    '2012-01-01 00:00:00'

    1
    #

    Since December 31, 2011 is a Saturday and January 1, 2012 is a Sunday, the resulting value of the execution is 1. If the year specified for the start-datetime and end-datetime is different, but both the start-datetime and end-datetime are for the same week, the resulting execution value will be 0.

To Page Top

(4) Examples

Examples

Determine the difference between the datetime data in columns C1 and C2 in Table T1 in days.

SELECT TIMESTAMPDIFF(DAY,"C1","C2") FROM "T1"

[Figure]

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.