Upsolver
Contact Support
Search
⌃K
Links
Comment on page

Date functions

This page goes over the date functions in Upsolver.

ADD_TIME_ZONE_OFFSET

Add a timezone offset to a date.
The timezone offset can be in any of the standard formats accepted by Java's ZoneId.of method (America/New_York, +02:00, etc.).

Inputs

  • time
  • zone
time
zone
result
"2018-03-10T08:55:12.456Z"
"+02:00"
"2018-03-10T10:55:12.456Z"
"2018-03-10T08:55:12.456Z"
"America/New_York"
"2018-03-10T03:55:12.456Z"
"2018-03-10T08:55:12.456Z"
""
null
"2018-03-10T08:55:12.456Z"
"Invalid Zone Id"
null

BETWEEN

Checks if a date is between two dates.

Inputs

  • value
  • lowerBound
  • upperBound

DATE_DIFF

Returns an error message as DATE_DIFF has been deprecated.

DATE_DIFF_PRECISE

This function returns the floating point number of time units between two timestamp values.

Syntax

DATE_DIFF_PRECISE(UNIT, TIMESTAMP1, TIMESTAMP2)

Arguments

UNIT The unit of time of the calculation.
TIMESTAMP1, TIMESTAMP2 The two timestamps used in the calculation (timestamp2-timestamp1)

Returns

Returns a floating point value for timestamp2-timestamp1 expressed in terms of unit.

Examples

> SELECT DATE_DIFF_PRECISE('day',TO_DATE('2018-01-01 00:00:00.000'), TO_DATE('2018-01-09 12:00:00.000'))FROM “Bid_Requests”;
- > 8.5

DATE_FORMAT

Convert date into string.

Properties

  • Format - The date format to output.
    • The format uses the Java DateTimeFormatter format.
    • For example, yyyy-MM-dd HH:mm:ss will output strings in the format 2017-07-23 14:33:54.
    • yyyy-MM-dd'T'HH:mm:ss.SSS'Z' will output strings in the format 2017-07-23T14:33:54.756Z.
time
zone
result
"2018-03-10T08:55:12.456Z"
"yyyy/MM/dd HH:mm:ss.SSS"
"2018/03/10 08:55:12.456"
"2018-03-10T08:55:12.456Z"
"yyyy/MM/dd"
"2018/03/10"
"2018-03-10T08:55:12.456Z"
"HH:mm:ss.SS"
"08:55:12.45"
"2018-03-10T08:55:12.456Z"
"E"
"Sat"

PARSE_DATE

Convert a date string in the provided format into a date.
If the date is missing or not in the correct format, this feature will not return a value.

Properties

  • Format - The date format to output.
    • The format uses the Java DateTimeFormatter format.
    • For example, yyyy-MM-dd HH:mm:ss will output strings in the format 2017-07-23 14:33:54.
    • yyyy-MM-dd'T'HH:mm:ss.SSS'Z' will output strings in the format 2017-07-23T14:33:54.756Z.
date
Format
result
"2017-09-25 22:11:00"
"yyyy-MM-dd HH:mm:ss"
"2017-09-25T22:11:00Z"
"2018-03-19"
"yyyy-MM-dd"
"2018-03-19T00:00:00Z"
"2018_Mar_21_17:59:05.689"
"yyyy_MMM_dd_HH:mm:ss.SSS"
"2018-03-21T17:
59:05.689Z"
"2018_Mar_21"
"yyyy_MMM_dd"
"2018-03-21T00:00:00Z"
"2018_Mar_21_24:59:05.689 America/Los_Angeles"
"yyyy_MMM_dd_kk:
mm:ss.SSS VV"
"2018-03-21T07:
59:05.689Z"

PRESTO_DATE_DIFF

This function returns the integer number of time units between two timestamp values.

Syntax

PRESTO_DATE_DIFF(UNIT, TIMESTAMP1, TIMESTAMP2)

Arguments

UNIT The unit of time of the calculation.
TIMESTAMP1, TIMESTAMP2 The two timestamps used in the calculation (timestamp2-timestamp1)

Returns

Returns an integer value, for timestamp2-timestamp1), expressed in terms of unit.

Examples

> SELECT PRESTO_DATE_DIFF('day', TO_DATE('2018-01-01 00:00:00.000'), TO_DATE('2018-01-09 00:00:01.000')) FROM “Bid_Requests”;
- > 8

TO_UNIX_EPOCH_MILLIS

Convert a date to its Epoch (unix) milliseconds representation.
date
result
"2018-03-10T08:55:12.456Z"
1520672112456

TO_UNIX_EPOCH_SECONDS

Convert a date to its Epoch (unix) seconds representation.
date
result
"2018-03-10T08:55:12.456Z"
1520672112

UNIX_EPOCH_TO_DATE

Convert epoch seconds or milliseconds to a date.
epochMillis
result
1520672112456
"2018-03-10T08:55:12.456Z"
1520672112
"2018-03-10T08:55:12Z"
1520672112456000
"2018-03-10T08:55:12.456Z"
1520672112456000000
"2018-03-10T08:55:12.456Z"
Previous
Conditional functions
Next
External API functions
Last modified 1yr ago