Timestamp Functions

Timestamp functions let you manipulate and format timestamp values in SQL.

You can add or subtract durations, calculate differences, round values to specific units, or extract date parts. They also allow casting between timestamps and strings with custom patterns and can return the current time. Some functions accept an additional argument for units or formats. If the provided argument is not in the expected type or format, implicit casting is applied to convert it as needed. These functions can be used across SQL clauses such as SELECT, WHERE, or within other functions, for example, aggregate functions, wherever expressions are permitted.

Below are a few timestamp functions with examples. To understand all the supported timestamp functions with examples, see Functions on Timestamps, in SQL Reference Guide.

Table 12-1 Timestamp functions

Function Description
timestamp_add Adds a duration to a timestamp value.
timestamp_floor Rounds-down the timestamp value to the specified unit.
format_timestamp Converts a timestamp into a string according to the specified pattern and the timezone.
timestamp_bucket Rounds the timestamp value to the beginning of the specified interval, starting from a specified origin value.

The following topics offer a few examples of how to use timestamp functions. For detailed syntax and examples of all available functions, please refer to the Functions on Timestamps in SQL Reference Guide.

timestamp_add function

Adds a duration to a timestamp value and returns the new timestamp. The duration can be positive or negative. The result type is TIMESTAMP(9).

Syntax:
TIMESTAMP(9) timestamp_add(TIMESTAMP timestamp, STRING duration)
Semantics
  • timestamp: A TIMESTAMP value or a value that can be cast to TIMESTAMP.
  • duration: A STRING with format [-](<n> <UNIT>)+, where 'n' is a number and the <UNIT> can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND or the plural form of these keywords (e.g. YEARS).

    Note:

    The UNIT keyword is not case-sensitive.
  • Return Value: TIMESTAMP(9)

Example: Add a few minutes to a person's last login time. 

SELECT timestamp_add(person.lastLogin, "2 minutes") AS Login_Time FROM Persons person WHERE id=5

Explanation: The timestamp_add function lets you add a few minutes to a person’s last login time, effectively increasing the stored timestamp by a small interval. This adjustment can be used to account for delays, simulate future login times, or test system behavior.

Output:
{"Login_Time":"2016-11-08T09:18:46.392900000Z"}

timestamp_floor

The timestamp_floor function returns the rounded-down value of the given timestamp to the specified unit. The functions can be used interchangeably in a query.

If the input timestamp value is already rounded down to the specified unit, then the return value is the same as the input timestamp value.

Syntax:
TIMESTAMP timestamp_floor(<timestamp>[, unit])
Semantics:
  • timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type.
  • unit: The unit argument is optional and of STRING data type. If not specified, DAY is the default unit. For more details, see Supported Units.
  • Return Value: TIMESTAMP(0)
    The function returns NULL in the following cases:
    • If either the timestamp or unit argument is set to NULL.
    • If the input timestamp is not castable to TIMESTAMP type.

Example: Print the last login rounded down to the hour of John Anderson 

SELECT timestamp_floor(person.lastLogin, 'HOUR') AS Last_Login FROM Persons person WHERE id=2

Explanation: You use the timestamp_floor function with the unit value as HOUR to round down the last login of the person, to the beginning of the hour.

This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

Output:
{"Last_Login":"2016-11-28T13:00:00Z"}

format_timestamp function

The format_timestamp function converts a timestamp into a string according to the specified pattern and the timezone.

Syntax:
STRING format_timestamp(<timestamp>,[pattern [, timezone])
Semantics:
  • timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to a TIMESTAMP type.
  • pattern: The pattern argument is optional and takes STRING data type as an input. It supports all pattern symbols in Java DateTimeFormatter class, except the timezone symbols 'z', 'zz', 'zzz', and 'v'. For more details on which timezone symbols are supported, see the table below:
    Symbol Meaning Presentation Example
    V time-zone ID zone-id America/Los_Angeles; Z; -08:30
    O localized zone-offset Offset-O GMT+8; GMT+08:00; UTC-08:00
    X zone-offset 'Z' for zero offset-X Z; -08; -0830; -08:30; -083015; -08:30:15
    x zone-offset offset-x +0000; -08; -0830; -08:30; -083015; -08:30:15
    Z zone-offset offset-Z +0000; -0800; -08:00

    Note:

    The default pattern is ISO-8601 format: yyyy-MM-dd'T'HH:mm:ss[.S..S].
  • timezone: The timezone argument is optional and takes STRING data type as an input.

    The timezone argument uses TimeZoneID (an identifier that represents a specific timezone). For example, use well-defined name such as "Asia/Calcutta", or a custom ID such as "GMT-08:00". For more examples, see List of TimeZoneID. Default is UTC.

    Note:

    Except for UTC and GMT, use the well-defined names for the timezones instead of abbreviations (for example, PST, IST).
  • Return Value: STRING

    The function returns NULL in the following cases:

    • If the timestamp, pattern, or timezone argument is set to NULL.
    • If the input timestamp is not castable to TIMESTAMP type.

Example: Print the last login time of the person according to the pattern and the timezone entered. 

SELECT id, firstname, format_timestamp(person.lastLogin, "MMM dd, yyyy HH:mm:ss O", "America/Vancouver") AS Formatted_Timestamp FROM Persons person WHERE id=1

Explanation: In this query, you specify the lastLogin field, pattern, and full name of the timezone as arguments to the format_timestamp function to convert the timestamp string to the specified "MMM dd, yyyy HH:mm:ss" pattern.

Note:

The letter 'O' in the pattern argument represents the ZoneOffset, which prints the amount of time that differs from Greenwich/UTC in the resulting string.
Output:
{"id":1,"firstname":"David","Formatted_Timestamp":"Oct 29, 2016 11:43:59 GMT-7"}

timestamp_bucket function

The timestamp_bucket function rounds the given timestamp value to the beginning of the specified interval (bucket). The interval starts at a specified origin on the timeline.

You can use this function for aggregating time series data to a desired time interval, known as periodicity. In certain cases, it is desirable to place all your time series data into equidistant buckets of given periodicity, with each bucket representing the same amount of time.

Syntax:
TIMESTAMP timestamp_bucket(<timestamp>[, interval [,origin ])
Semantics:
  • timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type.
  • interval: The interval argument is optional and a STRING data type. The interval is specified as <n> unit.

    where,

    n specifies the value of the interval. The n must be > 0

    unit defines the interval component. The function supports WEEK, DAY, HOUR, MINUTE, and SECOND in either singular or plural format.

    For example, "5 MINUTE" or "5 MINUTES".

    Note:

    The units are not case-sensitive.
  • origin: The origin argument represents the starting point of buckets on the timeline. This argument is optional and takes a TIMESTAMP value. The origin can be of any data type that can be cast to TIMESTAMP type. If not specified, Unix epoch 1970-01-01 is the default value.

    Note:

    The function also rounds the input timestamps that are lesser than the origin to the beginning of the specified interval. That is, you can supply an origin with a future timestamp value as compared to the input timestamp value on the timeline.
  • Return Value: TIMESTAMP(9)
    The function returns NULL in the following cases:
    • If any of the arguments are set to NULL.
    • If the input timestamp is not castable to TIMESTAMP type.

Example: Group last login times into one week intervals. 

SELECT id, firstname, timestamp_bucket(person.lastLogin,'1 week', '2016-11-28') AS Duration FROM Persons person

Explanation: In the above query, the timestamp_bucket function takes each person’s last login timestamp and rounds it down to the start of the one week interval, with the intervals starting from 2016-11-28. This enables you to group or analyze logins by weekly periods.

Output:
{"id":1,"firstname":"David","Duration":"2016-10-24T00:00:00.000000000Z"}
{"id":4,"firstname":"Peter","Duration":"2016-10-17T00:00:00.000000000Z"}
{"id":5,"firstname":"Dana","Duration":"2016-11-07T00:00:00.000000000Z"}
{"id":2,"firstname":"John","Duration":"2016-11-28T00:00:00.000000000Z"}
{"id":3,"firstname":"John","Duration":"2016-11-28T00:00:00.000000000Z"

For more examples on all the supported Timestamp functions, see Functions on Timestamps.