DATEDIFF()

Documentation

VoltDB Home » Documentation » Using VoltDB

DATEDIFF()

DATEDIFF() — Returns the difference between two timestamps or dates as an integer.

Synopsis

DATEDIFF( time-unit, timestamp-or-date, timestamp-or-date )

Description

The DATEDIFF() function returns an integer value representing the difference between two timestamps or dates for the specified time unit. Specifically, the second value minus the first value, measured in the specified unit. Note that both values must be of the same datatype — either TIMESTAMP or DATE — and the values are converted to the specified time unit before the subtraction occurs. So, for example, the difference between the timestamps Dec-31-1999 11:59 and Jan-01-2000 01:01 measured in days would be 1 day, but in hours it would be 2 hours, and in minutes 62 minutes.

The valid time unit keywords are:

  • MICROSECOND (or MICROS)

  • MILLISECOND (or MILLIS)

  • SECOND

  • MINUTE

  • HOUR

  • DAY

  • MONTH

  • QUARTER

  • YEAR

Example

The following example uses the DATEDIFF() function to return the number of whole minutes between the start and end of an event.

INSERT INTO billing 
    (account_id, when, length)
    SELECT user, start_time,
       DATEDIFF(MINUTE,start_time, end_time)
       FROM event;