1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. Supported units

Supported units

The unit is an identifier that specifies the precision to be considered while rounding the input timestamp. The unit takes a STRING value. You can supply a unit as the second argument to the following timestamp functions: timestamp_ceil, timestamp_floor, timestamp_round, and timestamp_trunc.

You can specify the following units. If not specified, the unit defaults to DAY.

Note:

The units are not case-sensitive.

Table 12-6 Description and Examples of Units

Units Description
YEAR, IYEAR
  • YEAR: The first day of a year.
  • IYEAR: The Monday of the first calendar week as defined by the ISO 8601 standard.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'YEAR') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'YEAR') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'YEAR') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'YEAR') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'YEAR') AS ROUND1, 
timestamp_round('2019-10-21T14:16:00Z', 'YEAR') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'YEAR') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'YEAR') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2020-01-01T00:00:00Z",
  "CEIL2" : "2020-01-01T00:00:00Z",
  "FLOOR1" : "2019-01-01T00:00:00Z",
  "FLOOR2" : "2019-01-01T00:00:00Z",
  "ROUND1" : "2019-01-01T00:00:00Z",
  "ROUND2" : "2020-01-01T00:00:00Z",
  "TRUNC1" : "2019-01-01T00:00:00Z",
  "TRUNC2" : "2019-01-01T00:00:00Z"
}
QUARTER The first day of the quarter as defined by the ISO 8601 standard.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'QUARTER') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'QUARTER') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS ROUND1, 
timestamp_round('2019-11-21T14:16:00Z', 'QUARTER') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'QUARTER') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'QUARTER') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-07-01T00:00:00Z",
  "CEIL2" : "2020-01-01T00:00:00Z",
  "FLOOR1" : "2019-04-01T00:00:00Z",
  "FLOOR2" : "2019-10-01T00:00:00Z",
  "ROUND1" : "2019-04-01T00:00:00Z",
  "ROUND2" : "2020-01-01T00:00:00Z",
  "TRUNC1" : "2019-04-01T00:00:00Z",
  "TRUNC2" : "2019-10-01T00:00:00Z"
}
MONTH The first day of the month.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'MONTH') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'MONTH') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'MONTH') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'MONTH') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'MONTH') AS ROUND1, 
timestamp_round('2019-11-21T14:16:00Z', 'MONTH') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'MONTH') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'MONTH') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-06-01T00:00:00Z",
  "CEIL2" : "2019-11-01T00:00:00Z",
  "FLOOR1" : "2019-05-01T00:00:00Z",
  "FLOOR2" : "2019-10-01T00:00:00Z",
  "ROUND1" : "2019-05-01T00:00:00Z",
  "ROUND2" : "2019-12-01T00:00:00Z",
  "TRUNC1" : "2019-05-01T00:00:00Z",
  "TRUNC2" : "2019-10-01T00:00:00Z"
}
WEEK,IWEEK
  • WEEK: Same day of the week as 1st of January.

    For example: If the first day of the year in the given timestamp is a Tuesday, using the WEEK unit rounds the timestamp up or down to the nearest Tuesday depending on the timestamp function used.

  • IWEEK: The first day of the calendar week as defined by the ISO 8601 standard, which is Monday.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'WEEK') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'WEEK') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'WEEK') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'WEEK') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'WEEK') AS ROUND1, 
timestamp_round('2019-11-15T05:00:00Z', 'WEEK') AS ROUND2, 
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'WEEK') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'WEEK') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-05-21T00:00:00Z",
  "CEIL2" : "2019-10-22T00:00:00Z",
  "FLOOR1" : "2019-05-14T00:00:00Z",
  "FLOOR2" : "2019-10-15T00:00:00Z",
  "ROUND1" : "2019-05-14T00:00:00Z",
  "ROUND2" : "2019-11-12T00:00:00Z",
  "TRUNC1" : "2019-05-14T00:00:00Z",
  "TRUNC2" : "2019-10-15T00:00:00Z"
}
DAY Day.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'DAY') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'DAY') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'DAY') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'DAY') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'DAY') AS ROUND1, 
timestamp_round('2019-11-15T15:00:00Z', 'DAY') AS ROUND2, 
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'DAY') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'DAY') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-05-15T00:00:00Z",
  "CEIL2" : "2019-10-22T00:00:00Z",
  "FLOOR1" : "2019-05-14T00:00:00Z",
  "FLOOR2" : "2019-10-21T00:00:00Z",
  "ROUND1" : "2019-05-14T00:00:00Z",
  "ROUND2" : "2019-11-16T00:00:00Z",
  "TRUNC1" : "2019-05-14T00:00:00Z",
  "TRUNC2" : "2019-10-21T00:00:00Z"
}
HOUR Hour.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'HOUR') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:00Z', 'HOUR') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'HOUR') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:00Z', 'HOUR') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'HOUR') AS ROUND1, 
timestamp_round('2019-11-15T15:30:00Z', 'HOUR') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'HOUR') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:00Z', 'HOUR') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-05-14T12:00:00Z",
  "CEIL2" : "2019-10-21T15:00:00Z",
  "FLOOR1" : "2019-05-14T11:00:00Z",
  "FLOOR2" : "2019-10-21T14:00:00Z",
  "ROUND1" : "2019-05-14T12:00:00Z",
  "ROUND2" : "2019-11-15T16:00:00Z",
  "TRUNC1" : "2019-05-14T11:00:00Z",
  "TRUNC2" : "2019-10-21T14:00:00Z"
}
MINUTE Minute.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:24Z', 'MINUTE') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:24Z', 'MINUTE') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS ROUND1, 
timestamp_round('2019-11-15T15:30:24Z', 'MINUTE') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.000000000Z', 'MINUTE') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:24Z', 'MINUTE') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-05-14T11:48:00Z",
  "CEIL2" : "2019-10-21T14:17:00Z",
  "FLOOR1" : "2019-05-14T11:47:00Z",
  "FLOOR2" : "2019-10-21T14:16:00Z",
  "ROUND1" : "2019-05-14T11:48:00Z",
  "ROUND2" : "2019-11-15T15:30:00Z",
  "TRUNC1" : "2019-05-14T11:47:00Z",
  "TRUNC2" : "2019-10-21T14:16:00Z"
}
SECOND Second.

Example:

SELECT timestamp_ceil('2019-05-14T11:47:56.4999Z', 'SECOND') AS CEIL1, 
timestamp_ceil('2019-10-21T14:16:24.99999Z', 'SECOND') AS CEIL2,
timestamp_floor('2019-05-14T11:47:56.6999Z', 'SECOND') AS FLOOR1, 
timestamp_floor('2019-10-21T14:16:24.500Z', 'SECOND') AS FLOOR2,
timestamp_round('2019-05-14T11:47:56.4999Z', 'SECOND') AS ROUND1, 
timestamp_round('2019-11-15T15:30:24.6999Z', 'SECOND') AS ROUND2,
timestamp_trunc('2019-05-14T11:47:56.355Z', 'SECOND') AS TRUNC1, 
timestamp_trunc('2019-10-21T14:16:24.7000Z', 'SECOND') AS TRUNC2
FROM BaggageInfo WHERE ticketNo=1762344493810

Output:

{
  "CEIL1" : "2019-05-14T11:47:57Z",
  "CEIL2" : "2019-10-21T14:16:25Z",
  "FLOOR1" : "2019-05-14T11:47:56Z",
  "FLOOR2" : "2019-10-21T14:16:24Z",
  "ROUND1" : "2019-05-14T11:47:56Z",
  "ROUND2" : "2019-11-15T15:30:25Z",
  "TRUNC1" : "2019-05-14T11:47:56Z",
  "TRUNC2" : "2019-10-21T14:16:24Z"
}