Functions on Timestamps

The timestamp functions perform various operations on the supplied timestamps.

You can add a duration to a timestamp, find the difference between two timestamps, and round timestamp to a specified unit. You can cast a timestamp to/from string with customized patterns. Some of the functions support the extraction of the date part of a timestamp. You can also use these functions to display the current time.

A few of these timestamp functions take a second argument, which specifies the units or the format patterns.

The input timestamp can be a TIMESTAMP value/expression. If required, the timestamp functions perform an implicit CAST on the supplied timestamp.

You can invoke the timestamp functions from the SELECT/WHERE clauses, and also from other SQL clauses where function calls are allowed in the syntax. For example, you can supply the functions as arguments to Aggregate Functions.

The following timestamp functions are supported:

Table 12-1 Timestamp functions

Function	Description
timestamp_add	Adds a duration to a timestamp value.
timestamp_diff	Returns the number of milliseconds between two timestamp values.
get_duration	Converts the given number of milliseconds to a duration string.
timestamp_ceil	Rounds-up the timestamp value to the specified unit.
timestamp_floor/timestamp_trunc	Rounds-down the timestamp value to the specified unit.
timestamp_round	Rounds the timestamp value to the specified unit.
timestamp_bucket	Rounds the timestamp value to the beginning of the specified interval, starting from a specified origin value.
format_timestamp	Converts a timestamp into a string according to the specified pattern and the timezone.
parse_to_timestamp	Converts a string in the specified pattern into a timestamp value.
to_last_day_of_month	Returns the last day of the month from a given timestamp.
Timestamp extract functions	Extracts the corresponding date part of a given timestamp. The following functions are supported: year month day hour minute second millisecond microsecond nanosecond Returns the week number within the year. The following functions are supported: week isoweek Returns the corresponding index from a given timestamp. The following functions are supported: quarter day_of_week day_of_month day_of_year
current_time_millis	Returns the current time as the number of milliseconds.
current_time	Returns the current time as a timestamp value.

The examples in the following sections are based on an airline baggage tracking application and a TV streaming application. To follow along with the examples, create the tables and load data as described in Tables used in the examples section.