TRUNC

Syntax

TRUNC
(
  expr  IN date [, fmt   IN text]
)
RETURNS date;

TRUNC
(
  expr  IN timestamp without time zone [, fmt   IN text]
)
RETURNS timestamp without time zone;

TRUNC
(
  expr  IN timestamp with time zone [, fmt   IN text]
)
RETURNS timestamp with time zone;

Overview

The TRUNC function truncates subunits from a date or timestamp value and returns the truncated (discarded) result to fit the specified format model.

In other words, it removes the decimal part (or detail units) from the time or date part of the value, leaving only the desired units (e.g., year, month, day, etc.).

If no format string (fmt) is provided, it defaults to 'DDD' (day in days out).

For the timestamp version, the internal time information is processed together to truncate sub-hour increments, and sub-second information is set to zero.

Parameters

Parameter	Description
expr	date, timestamp, timestamptz type; the target date or timestamp value to truncate. If this value is NULL, the result will also be NULL.
fmt	text type; A format string that specifies which units the date or timestamp value should be truncated to. For example, you can specify ‘YYYY’ (year), ‘MM’ (month), ‘DDD’ (day), etc. If this value is NULL, the default value (e.g., ‘DDD’) is used.

Example

# test 1
-- date truncation: truncates '2023-05-15' to the month, returning the first day of the month
SELECT oracle.TRUNC('2023-05-15'::date, 'MONTH');
-- result: '2023-05-01'

   trunc    
------------
 2023-05-01
(1 row)

# test 2
-- date truncation: when unformatted, the original date is returned as is.
SELECT oracle.TRUNC('2023-05-15'::date);
-- result: '2023-05-15'

   trunc    
------------
 2023-05-15\q
 
(1 row)

# test 3
-- truncate timestamp: truncates '2023-05-15 14:35:20' to hours, removing minutes and seconds
SELECT oracle.TRUNC('2023-05-15 14:35:20'::timestamp, 'HH24');
-- result: '2023-05-15 14:00:00'

        trunc        
---------------------
 2023-05-15 14:00:00
(1 row)

# test 4
-- timestamptz truncation: for '2023-05-15 14:35:20+09',if no format is specified, it is truncated to the default format (‘DDD’).
SELECT oracle.TRUNC('2023-05-15 14:35:20+09'::timestamptz);
-- result: returns the result with timestamp values truncated (truncated by days)

         trunc          
------------------------
 2023-05-15 00:00:00+09
(1 row)