Datetime Functions and Operators
Manipulate the datetime values with the following datetime functions supported by the query service.
Date and timestamp formats supported by the datetime functions. Use a hyphen - as a separator between the date values (year, month, week, and day) and a colon : as a separator between time values (hours, minutes, and seconds). If subseconds are to be specified, use a period . as a separator.
yyyy-MM-dd HH:mm:ss.SSSZZZZyyyy-MM-dd HH:mm:ss.SSS
Units of time supported by the datetime functions.
- millisecond
- second
- minute
- hour
- day
- week
- month
- quarter
- year
| Function | Return Type | Input Arguments | Description |
|---|---|---|---|
current_date() | Date | None | Returns the current date as of the start of the query. |
current_time() | Time | None | Returns the current time as of the start of the query. |
current_timestamp(p) | Timestamp with time zone | Takes no input arguments by default.
| Returns the current timestamp with time zone as of the start of the query, with precision up to three digits of subsecond accuracy. You can optionally specify the precision of subseconds required. |
localtime() | Time | None | Returns the current local time at the start of the query. |
localtimestamp() | Timestamp with time zone | None | Returns the current local timestamp as of at the start of the query, up to three-digit subsecond precision. |
current_timezone() | Varchar | None | Returns the timezone of the server. |
date_add(unit, interval, timestamp) | Same as input timestamp. |
| Use this function to add an interval value of type unit to the input timestamp. You can use a negative interval value and perform subtraction on the input timestamp. For example:
|
date_diff(unit, timestamp1, timestamp2) | Bigint |
| Use this function to find the difference between two input timestamps. It returns timestamp2 minus timestamp1 expressed in terms of the specified unit of time. For example:
|
date_format(timestamp, formating_string) | Varchar |
| Returns a formatted timestamp as a string using the specified input format. Refer to Format Specifiers to learn more about constructing the formatting string. |
date_parse(string, formatting_string) | Timestamp |
| Parses the input string into a timestamp using the input format. Refer to Format Specifiers to learn more about constructing the formatting string. For example:
|
date_trunc(unit, timestamp) | Timestamp |
| Returns the truncated input timestamp by the input units. For example:
|
from_iso8601_date(string) | Date |
| Parses the date string into a date. The input string can be a calendar date, a week date using ISO week numbering, or year and day of year combined. For example:
|
from_iso8601_timestamp(string) | Timestamp with time zone |
| Parses ISO 8601 formatted date string into a timestamp. The input string can be a calendar date, a week date using ISO week numbering, or year and day of year combined. For example:
|
from_unixtime(unixtime_in_seconds) | Timestamp with time zone |
| Converts the Unix time in seconds to date and timestamp. The unixtime_in_seconds is the number of seconds since 1970-01-01 00:00:00 UTC. For example:
|
from_unixtime_nanos(unixtime_in_ns) | Timestamp with time zone |
| Returns the UNIX timestamp as a timestamp with time zone. The argument unixtime_in_ns is the number of nanoseconds since 1970-01-01 00:00:00.000000000 UTC. |
human_readable_seconds(seconds) | Varchar |
| Converts the input in seconds into a human-readable string with weeks, days, hours, minutes, and seconds. For example:
|
now() | Timestamp with time zone | None | Returns the current timestamp with time zone as of the start of the query, with precision up to three digits of subsecond accuracy. |
parse_duration(string) | Interval |
| Parses the input string with the format value unit into a time interval, where value is a fractional number and unit is the unit of time. For example:
|
to_iso8601(arg) | Varchar |
| Formats the input as an ISO 8601 string. For example:
|
with_timezone(timestamp, timezone) | Timestamp with time zone |
| Returns a timestamp with time zone from timestamp with precision p and timezone. For example:
|
| Format Specifier | Usage |
|---|---|
%a | Abbreviated weekday name (Sun to Sat) |
%b | Abbreviated month name (Jan to Dec) |
%c | Month, numeric (1 to 12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, and so on) |
%d | Day of the month, numeric (01 to 31) |
%e | Day of the month, numeric (1 to 31) |
%f | Fraction of second (six digits for printing: 000000 to 999000; one to nine digits for parsing: 0 to 999999999) |
%H | Hour (00 to 23) |
%h | Hour (01 to 12) |
%I | Hour (01 to 12) |
%i | Minutes, numeric (00 to 59) |
%j | Day of year (001 to 366) |
%k | Hour (0 to 23) |
%l | Hour (1 to 12) |
%M | Month name (January to December) |
%m | Month, numeric (01 to 12) |
%p | AM or PM |
%r | Time of day, 12-hour (equivalent to %h:%i:%s %p) |
%S | Seconds (00 to 59) |
%s | Seconds (00 to 59) |
%T | Time of day, 24-hour (equivalent to %H:%i:%s) |
%U | Week (00 to 53), where Sunday is the first day of the week |
%u | Week (00 to 53), where Monday is the first day of the week |
%V | Week (01 to 53), where Sunday is the first day of the week; used with %X |
%v | Week (01 to 53), where Monday is the first day of the week; used with %x |
%W | Weekday name (Sunday to Saturday) |
%w | Day of the week (0 to 6), where Sunday is the first day of the week |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
%x | x, for any x not listed in earlier rows |