Comment on page
Date functions
This page goes over the date functions in Upsolver.
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.).- 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 |
Checks if a date is between two dates.
- value
- lowerBound
- upperBound
Returns an error message as DATE_DIFF has been deprecated.
This function returns the floating point number of time units between two timestamp values.
DATE_DIFF_PRECISE(UNIT, TIMESTAMP1, TIMESTAMP2)
UNIT
The unit of time of the calculation. TIMESTAMP1, TIMESTAMP2
The two timestamps used in the calculation
(timestamp2-timestamp1)
Returns a floating point value for timestamp2-timestamp1 expressed in terms of unit.
> 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
Convert date into string.
- Format - The date format to output.
- For example,
yyyy-MM-dd HH:mm:ss
will output strings in the format2017-07-23 14:33:54
. yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
will output strings in the format2017-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" |
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.
- Format - The date format to output.
- For example,
yyyy-MM-dd HH:mm:ss
will output strings in the format2017-07-23 14:33:54
. yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
will output strings in the format2017-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" |
This function returns the integer number of time units between two timestamp values.
PRESTO_DATE_DIFF(UNIT, TIMESTAMP1, TIMESTAMP2)
UNIT
The unit of time of the calculation. TIMESTAMP1, TIMESTAMP2
The two timestamps used in the calculation
(timestamp2-timestamp1)
Returns an integer value, for
timestamp2-timestamp1)
, expressed in terms of unit
. > 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
Convert a date to its Epoch (unix) milliseconds representation.
date | result |
"2018-03-10T08:55:12.456Z" | 1520672112456 |
Convert a date to its Epoch (unix) seconds representation.
date | result |
"2018-03-10T08:55:12.456Z" | 1520672112 |
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" |
Last modified 1yr ago