DATE_DIFF function in Amazon QLDB - Amazon Quantum Ledger Database (Amazon QLDB)
SyntaxArgumentsReturn typeExamplesRelated functions

DATE_DIFF function in Amazon QLDB

In Amazon QLDB, use the DATE_DIFF function to return the difference between the specified date parts of two given timestamps.

Syntax

DATE_DIFF( datetimepart, timestamp1, timestamp2 )

Arguments

datetimepart

The date or time part that the function operates on. This parameter can be one of the following:

  • year

  • month

  • day

  • hour

  • minute

  • second

timestamp1, timestamp2

The two field names or expressions of data type timestamp that the function compares. If timestamp2 is later than timestamp1, the result is positive. If timestamp2 is earlier than timestamp1, the result is negative.

An Ion timestamp literal value can be denoted with backticks (`...`). For formatting details and examples of timestamp values, see Timestamps in the Amazon Ion specification document.

Return type

int

Examples

DATE_DIFF(year, `2010-01-01T`, `2011-01-01T`)            -- 1
DATE_DIFF(year, `2010-12T`, `2011-01T`)                  -- 0 (must be at least 12 months apart to evaluate as a 1 year difference)
DATE_DIFF(month, `2010T`, `2010-05T`)                    -- 4 (2010T is equivalent to 2010-01-01T00:00:00.000Z)
DATE_DIFF(month, `2010T`, `2011T`)                       -- 12
DATE_DIFF(month, `2011T`, `2010T`)                       -- -12
DATE_DIFF(month, `2010-12-31T`, `2011-01-01T`)           -- 0 (must be at least a full month apart to evaluate as a 1 month difference)
DATE_DIFF(day, `2010-01-01T23:00Z`, `2010-01-02T01:00Z`) -- 0 (must be at least 24 hours apart to evaluate as a 1 day difference)

-- Runnable statements
SELECT DATE_DIFF(year, `2010-01-01T`, `2011-01-01T`) FROM << 0 >> -- 1
SELECT DATE_DIFF(month, `2010T`, `2010-05T`) FROM << 0 >>         -- 4

Related functions