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"