Date and time functions
#
systimestampsystimestamp()
- offset from UTC Epoch in microseconds. Calculates
UTC timestamp
using system's real time clock. The value is affected by
discontinuous jumps in the system time (e.g., if the system administrator
manually changes the system time).
Arguments:
systimestamp()
does not accept arguments.
Return value:
Return value type is timestamp
.
Examples:
ts | reading |
---|---|
2020-01-02T19:28:48.727516Z | 123.5 |
#
sysdatesysdate()
- returns the timestamp of the host system as a date
with
millisecond
precision.
Calculates UTC date
with millisecond precision using system's real time clock.
The value is affected by discontinuous jumps in the system time (e.g., if the
system administrator manually changes the system time).
Arguments:
sysdate()
does not accept arguments.
Return value:
Return value type is date
.
Examples:
sysdate | reading |
---|---|
2020-01-02T19:28:48.727516Z | 123.5 |
#
nownow()
- offset from UTC Epoch in microseconds.
Calculates UTC timestamp
using system's real time clock. Unlike
sysdatetime()
, it does not change within the query execution timeframe and
should be used in WHERE clause to filter designated timestamp column relative to
current time, i.e.:
SELECT now() FROM long_sequence(200)
will return the same timestamp for all rowsSELECT systimestamp() FROM long_sequence(200)
will have new timestamp values for each row
Arguments:
now()
does not accept arguments.
Return value:
Return value type is timestamp
.
Examples:
created | origin |
---|---|
2021-02-01T21:51:34.443726Z | 1 |
now |
---|
2021-02-01T21:51:34.443726Z |
2021-02-01T21:51:34.443726Z |
2021-02-01T21:51:34.443726Z |
#
to_timestampto_timestamp(string, format)
- converts string to timestamp
by using the
supplied format
to extract the value.
Will convert a string
to timestamp
using the format definition passed as an
argument. When the format
definition does not match the string
input, the
result will be null
.
For more information about recognized timestamp formats, see the date and timestamp format section.
Arguments:
string
is any string that represents a date and/or time.format
is a string that describes thetimestamp format
in whichstring
is expressed.
Return value:
Return value type is timestamp
Examples:
to_timestamp |
---|
2020-03-01T15:43:21.000000Z |
to_timestamp |
---|
null |
timestamp | value |
---|---|
2019-12-12T12:15:00.000000Z | 123.5 |
#
to_dateto_date(string, format)
- converts string to date
by using the supplied
format
to extract the value.
Will convert a string
to date
using the format definition passed as an
argument. When the format
definition does not match the string
input, the
result will be null
.
For more information about recognized timestamp formats, see the date and timestamp format section.
Arguments:
string
is any string that represents a date and/or time.format
is a string that describes thedate format
in whichstring
is expressed.
Return value:
Return value type is date
Examples:
to_date |
---|
2020-03-01T15:43:21.000Z |
to_date |
---|
null |
date | value |
---|---|
2019-12-12T12:15:00.000Z | 123.5 |
#
to_strto_str(value, format)
- converts date or timestamp value to a string in the
specified format
Will convert a date or timestamp value to a string using the format definition
passed as an argument. When elements in the format
definition are
unrecognized, they will be passed-through as string.
For more information about recognized timestamp formats, see the date and timestamp format section.
Arguments:
value
is anydate
ortimestamp
format
is a timestamp format.
Return value:
Return value type is string
Examples:
- Basic example
to_str |
---|
2020-03-04 |
- With unrecognized timestamp definition
to_str |
---|
2020-03-04 gooD DAY 123 |
#
dateadddateadd(period, n, startDate)
- adds n
period
to startDate
.
Arguments:
period
is a char. Period to be added. Available periods ares
,m
,h
,d
,M
,y
.n
is an int. Number of periods to add.startDate
is a timestamp or date. Timestamp to add the periods to.
Return value:
Return value type is timestamp
Examples:
systimestamp | dateadd |
---|---|
2020-04-17T00:30:51.380499Z | 2020-04-17T02:30:51.380499Z |
systimestamp | dateadd |
---|---|
2020-04-17T00:30:51.380499Z | 2020-04-19T00:30:51.380499Z |
systimestamp | dateadd |
---|---|
2020-04-17T00:30:51.380499Z | 2020-06-17T00:30:51.380499Z |
#
datediffdatediff(period, date1, date2)
- returns the absolute number of period
between date1
and date2
.
Arguments:
period
is a char. Period to be added. Available periods ares
,m
,h
,d
,M
,y
.date1
anddate2
are date or timestamp. Dates to compare
Return value:
Return value type is int
Examples:
datediff |
---|
4 |
datediff |
---|
1 |
#
millismillis(value)
- returns the millis
of the second for a given date or
timestamp from 0
to 999
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
millis |
---|
123 |
second | count |
---|---|
0 | 2323 |
1 | 6548 |
... | ... |
998 | 9876 |
999 | 2567 |
#
microsmicros(value)
- returns the micros
of the millisecond for a given date or
timestamp from 0
to 999
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
millis |
---|
456 |
second | count |
---|---|
0 | 2323 |
1 | 6548 |
... | ... |
998 | 9876 |
999 | 2567 |
#
secondsecond(value)
- returns the second
of the minute for a given date or
timestamp from 0
to 59
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
second |
---|
43 |
second | count |
---|---|
0 | 2323 |
1 | 6548 |
... | ... |
58 | 9876 |
59 | 2567 |
#
minuteminute(value)
- returns the minute
of the hour for a given date or timestamp
from 0
to 59
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
minute |
---|
43 |
minute | count |
---|---|
0 | 2323 |
1 | 6548 |
... | ... |
58 | 9876 |
59 | 2567 |
#
hourhour(value)
- returns the hour
of day for a given date or timestamp from 0
to 23
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
hour |
---|
12 |
hour | count |
---|---|
0 | 2323 |
1 | 6548 |
... | ... |
22 | 9876 |
23 | 2567 |
#
dayday(value)
- returns the day
of month for a given date or timestamp from 0
to 23
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
day |
---|
01 |
day | count |
---|---|
1 | 2323 |
2 | 6548 |
... | ... |
30 | 9876 |
31 | 2567 |
#
monthmonth(value)
- returns the month
of year for a given date or timestamp from
1
to 12
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
month |
---|
03 |
month | count |
---|---|
1 | 2323 |
2 | 6548 |
... | ... |
11 | 9876 |
12 | 2567 |
#
yearyear(value)
- returns the year
for a given date or timestamp
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
year |
---|
2020 |
year | count |
---|---|
2015 | 2323 |
2016 | 9876 |
2017 | 2567 |
#
is_leap_yearis_leap_year(value)
- returns true
if the year
of value
is a leap year,
false
otherwise.
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is boolean
Examples:
year | is_leap_year |
---|---|
2020 | true |
2021 | false |
2022 | false |
2023 | false |
2024 | true |
2025 | false |
#
days_in_monthdays_in_month(value)
- returns the number of days in a month from a provided
timestamp or date.
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
month | days_in_month |
---|---|
4 | 30 |
5 | 31 |
6 | 30 |
7 | 31 |
8 | 31 |
#
day_of_weekday_of_week(value)
- returns the day number in a week from 1
(Monday) to 7
(Sunday)
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
day | day_of_week |
---|---|
Monday | 1 |
Tuesday | 2 |
Wednesday | 3 |
Thursday | 4 |
Friday | 5 |
Saturday | 6 |
Sunday | 7 |
#
day_of_week_sunday_firstday_of_week_sunday_first(value)
- returns the day number in a week from 1
(Sunday) to 7
(Saturday)
Arguments:
value
is anytimestamp
ordate
Return value:
Return value type is int
Examples:
day | day_of_week_sunday_first |
---|---|
Monday | 2 |
Tuesday | 3 |
Wednesday | 4 |
Thursday | 5 |
Friday | 6 |
Saturday | 7 |
Sunday | 1 |
#
Date and Timestamp formatFormat is a combination of letters from table below combined with arbitrary text. Format letters are case-sensitive and are used as is (e.g. without any prefix)
Letter | Date or Time Component | Presentation | Examples |
---|---|---|---|
G | Era designator | Text | AD |
y | Year | Year | 1996; 96 |
Y | Week year | Year | 2009; 09 |
M | Month in year | Month | July; Jul; 07 |
w | Week in year | Number | 27 |
W | Week in month | Number | 2 |
D | Day in year | Number | 189 |
d | Day in month | Number | 10 |
F | Day of week in month | Number | 2 |
E | Day name in week | Text | Tuesday; Tue |
u | Day number of week (1 = Monday, ..., 7 = Sunday) | Number | 1 |
a | Am/pm marker | Text | PM |
H | Hour in day (0-23) | Number | 0 |
k | Hour in day (1-24) | Number | 24 |
K | Hour in am/pm (0-11) | Number | 0 |
h | Hour in am/pm (1-12) | Number | 12 |
m | Minute in hour | Number | 30 |
s | Second in minute | Number | 55 |
S | Millisecond | Number | 978 |
z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
Z | Time zone | RFC 822 time zone | -0800 |
X | Time zone | ISO 8601 time zone | -08; -0800; -08:00 |
U | Microsecond | Number | 698 |