TIMESTAMP_TRUNC

Truncates the specified TIMESTAMP. If you omit the precision argument, TIMESTAMP_TRUNC truncates to day (DD) precision.

Behavior Type

Syntax

TIMESTAMP_TRUNC( trunc‑target[, 'precision'] )

Parameters

trunc‑target

An expression that evaluates to one of the following data types:

precision

A string constant that specifies precision for the truncated value, one of the following:

  • Century: CC | SCC
  • Year: SYYY | YYYY | YEAR | YYY | YY | Y
  • ISO Year: IYYY | IYY | IY | I
  • Quarter: Q
  • Month: MONTH | MON | MM | RM
  • Same weekday as first day of year: WW
  • Same weekday as first day of ISO year: IW
  • Same weekday as first day of month: W
  • Day: DDD | DD | J
  • First weekday: DAY | DY | D
  • Hour: HH | HH12 | HH24
  • Minute: MI
  • Second: SS

Note: Hour, minute, and second truncating is not supported by DATE expressions.

Examples

Truncate to the current hour:

=> SELECT TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, 'HH');
   TIMESTAMP_TRUNC
---------------------
 2016-04-29 08:00:00
(1 row)

Truncate to the month:

=> SELECT TIMESTAMP_TRUNC('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
   TIMESTAMP_TRUNC
---------------------
 2011-09-01 00:00:00
(1 row)

See Also

TRUNC