Upsolver SQLake
Contact Support
Search
⌃K
Links
Comment on page

DATE_DIFF

Computes the difference between two timestamps.

Syntax

DATE_DIFF(UNIT, TIMESTAMP1, TIMESTAMP2)

Arguments

UNIT

Type: string
The unit of time to return.
It can be one of the following:
  • year
  • quarter
  • month
  • week
  • day
  • hour
  • minute
  • second
  • millisecond

TIMESTAMP1

Type: timestamp
The time to subtract from TIMESTAMP2.

TIMESTAMP2

Type: timestamp
The time TIMESTAMP1 is subtracted from.

Returns

Type: bigint
TIME2 - TIME1 expressed in terms of UNIT.

Examples

UNIT
TIMESTAMP1
TIMESTAMP2
Output
day
2001-08-22 03:04:05.321
2001-08-23 03:04:05.321
1
year
2001-08-22 03:04:05.321
2006-08-22 03:04:05.321
5
year
2006-08-22 03:04:05.321'
2001-08-22 03:04:05.321
-5
second
2001-08-22 03:04:05.321
2001-08-22 03:04:35.321
30
quarter
2001-08-22 03:04:05.321
2001-11-22 03:04:05.321
1
quarter
2001-08-22 03:04:05.321
2001-08-22 03:04:05.321
0
year
2001-08-22 03:04:05.321
null
null

Transformation job example

SQL

CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
"$event_timestamp",
"$commit_time",
DATE_DIFF('second', "$event_timestamp", "$commit_time") AS time_diff
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 1;

Query result

$event_timestamp
$commit_time
time_diff
2022-08-19 20:20:00.000
2022-08-19 20:21:00.000
60
Previous
DATE_ADD
Next
DATE_TRUNC
Last modified 3mo ago