Skip to main content

Time & Date Functions

Scalar functions for temporal data processing and manipulation that can be used in any expression context.

now()​

Returns the current timestamp.

Parameters​

This function takes no parameters.

Description​

The now() function returns the current date and time as a timestamp. The exact timestamp represents the moment when the function is evaluated during query execution. All calls to now() within the same query execution return the same timestamp value.

today()​

Returns today's date at midnight.

Parameters​

This function takes no parameters.

Description​

The today() function returns the current date with the time portion set to midnight (00:00:00). This is equivalent to truncating now() to the day boundary. The result represents the start of the current day.

yesterday()​

Returns yesterday's date at midnight.

Parameters​

This function takes no parameters.

Description​

The yesterday() function returns yesterday's date with the time portion set to midnight (00:00:00). This is equivalent to subtracting one day from today(). The result represents the start of the previous day.

tomorrow()​

Returns tomorrow's date at midnight.

Parameters​

This function takes no parameters.

Description​

The tomorrow() function returns tomorrow's date with the time portion set to midnight (00:00:00). This is equivalent to adding one day to today(). The result represents the start of the next day.

ts(timestamp)​

Converts a string to a timestamp.

Parameters​

  • timestamp - String expression representing a timestamp

Description​

The ts() function parses a string representation of a timestamp and converts it to a timestamp type. The function accepts various timestamp formats including ISO 8601 format. If the string cannot be parsed as a valid timestamp, an error is raised.

year(timestamp)​

Extracts the year from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The year() function extracts the year component from a timestamp and returns it as an integer. For example, a timestamp of "2023-07-15 14:30:00" would return 2023.

month(timestamp)​

Extracts the month from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The month() function extracts the month component from a timestamp and returns it as an integer from 1 to 12, where 1 represents January and 12 represents December. For example, a timestamp of "2023-07-15 14:30:00" would return 7.

day(timestamp)​

Extracts the day of the month from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The day() function extracts the day component from a timestamp and returns it as an integer from 1 to 31, depending on the month. For example, a timestamp of "2023-07-15 14:30:00" would return 15.

day_of_week(timestamp)​

Extracts the day of the week from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The day_of_week() function extracts the ISO day of the week from a timestamp and returns it as an integer from 1 (Monday) to 7 (Sunday).

hour(timestamp)​

Extracts the hour from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The hour() function extracts the hour component from a timestamp and returns it as an integer from 0 to 23, using 24-hour format. For example, a timestamp of "2023-07-15 14:30:00" would return 14.

minute(timestamp)​

Extracts the minute from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The minute() function extracts the minute component from a timestamp and returns it as an integer from 0 to 59. For example, a timestamp of "2023-07-15 14:30:00" would return 30.

second(timestamp)​

Extracts the second from a timestamp.

Parameters​

  • timestamp - Timestamp expression

Description​

The second() function extracts the second component from a timestamp and returns it as an integer from 0 to 59. For example, a timestamp of "2023-07-15 14:30:45" would return 45.

at_timezone(timestamp, timezone)​

Converts a timestamp to a different timezone.

Parameters​

  • timestamp - Timestamp expression to convert
  • timezone - String expression representing the target timezone

Description​

The at_timezone() function converts a timestamp from its current timezone to the specified target timezone. The timezone parameter should be a valid timezone identifier such as "UTC", "America/New_York", or "Europe/London". The function returns a new timestamp representing the same moment in time but expressed in the target timezone.

to_millis(interval)​

Converts an interval to milliseconds.

Parameters​

  • interval - Interval expression to convert

Description​

The to_millis() function converts an interval (duration) to its equivalent value in milliseconds as an integer. This is useful for calculations that require numeric representations of time durations. For example, an interval of "5 minutes" would return 300000 milliseconds.

to_nanos(interval)​

Converts an interval to nanoseconds.

Parameters​

  • interval - Interval expression to convert

Description​

The to_nanos() function converts an interval (duration) to its equivalent value in nanoseconds as an integer. This provides the highest precision for time duration calculations. The function multiplies the millisecond value by 1,000,000 to get nanoseconds. For example, an interval of "1 second" would return 1,000,000,000 nanoseconds.

from_millis(millis)​

Creates an interval from milliseconds.

Parameters​

  • millis - Integer expression representing milliseconds

Description​

The from_millis() function creates an interval from a millisecond value. This is the inverse of to_millis(), allowing you to convert numeric millisecond values back into interval types that can be used with timestamp arithmetic. For example, from_millis(5000) creates an interval of 5 seconds.

from_nanos(nanos)​

Creates an interval from nanoseconds.

Parameters​

  • nanos - Integer expression representing nanoseconds

Description​

The from_nanos() function creates an interval from a nanosecond value. This is the inverse of to_nanos(), converting numeric nanosecond values into interval types. The function divides the nanosecond value by 1,000,000,000 to convert to seconds. For example, from_nanos(1500000000) creates an interval of 1.5 seconds.

from_unixtime_seconds(seconds)​

Creates a timestamp from Unix seconds.

Parameters​

  • seconds - Integer expression representing seconds since Unix epoch

Description​

The from_unixtime_seconds() function converts a Unix timestamp (seconds since January 1, 1970 UTC) into a timestamp type. This is commonly used when working with systems that store time as Unix timestamps. For example, from_unixtime_seconds(1625097600) returns the timestamp "2021-07-01 00:00:00".

from_unixtime_millis(millis)​

Creates a timestamp from Unix milliseconds.

Parameters​

  • millis - Integer expression representing milliseconds since Unix epoch

Description​

The from_unixtime_millis() function converts Unix time in milliseconds to a timestamp. Many systems and APIs return timestamps as milliseconds since the Unix epoch. This function handles the conversion by multiplying the input by 1,000,000 to convert to nanoseconds internally. For example, from_unixtime_millis(1625097600000) returns "2021-07-01 00:00:00".

from_unixtime_micros(micros)​

Creates a timestamp from Unix microseconds.

Parameters​

  • micros - Integer expression representing microseconds since Unix epoch

Description​

The from_unixtime_micros() function converts Unix time in microseconds to a timestamp. This provides microsecond precision for systems that require it. The function multiplies the input by 1,000 to convert to nanoseconds internally. For example, from_unixtime_micros(1625097600000000) returns "2021-07-01 00:00:00".

from_unixtime_nanos(nanos)​

Creates a timestamp from Unix nanoseconds.

Parameters​

  • nanos - Integer expression representing nanoseconds since Unix epoch

Description​

The from_unixtime_nanos() function converts Unix time in nanoseconds directly to a timestamp. This provides the highest precision for timestamp conversion and is useful when working with high-frequency data or systems that track time at nanosecond granularity. For example, from_unixtime_nanos(1625097600000000000) returns "2021-07-01 00:00:00".

to_unixtime(timestamp)​

Converts a timestamp to Unix seconds.

Parameters​

  • timestamp - Timestamp expression to convert

Description​

The to_unixtime() function converts a timestamp to Unix time, returning the number of seconds since January 1, 1970 UTC as a double-precision floating-point number. The fractional part represents sub-second precision. This is useful for interoperability with systems that expect Unix timestamps. For example, the timestamp "2021-07-01 00:00:00" returns 1625097600.0.