Redshift Trunc and Date_Trunc Functions, Examples and Differences

Amazon Redshift supports many built-in functions that you can use to perform day to day activities such as extracting only date part from a timestamp value. Redshift provides many date and string functions that you can use to play around date type. In this article, we will check how to use an Amazon Redshift trunc and date_trunc functions, syntax, usage, examples and difference between these two functions.

Redshift Trunc and Date_Trunc Functions

You can use the trunc and date_trunc functions to trunc certain portions of the date type. However, you can use trunc to shorten the numeric expression.

Now let use check these functions in brief.

Redshift Trunc Function
Redshift Date_Trunc Function

Amazon Redshift Trunc Function

Redshift trunc function truncates the timestamp type and return date. However, you can use the same trunc function with numeric expression. You can truncate the decimal part to shorten the numeric expression.

Redshift Trunc Function Syntax

Following is the syntax of the Amazon Redshift TRUNC function.

TRUNC(timestamp)

TRUNC(numeric, trunc_no)

Redshift Trunc Function Examples

Consider following examples to understand the usage of TRUNC function.

Return only Date Part

You can truncate the time part and return only the date.

For example,

dev=# select sysdate;
         timestamp
----------------------------
 2020-09-15 14:05:59.076525
(1 row)

dev=# select trunc(sysdate);
   trunc
------------
 2020-09-15
(1 row)

Truncate Decimal Part

You can truncate the decimal part of the numeric value.

For example,

dev=# select trunc(2.12345);
 trunc
-------
     2
(1 row)

You can also provide the number of decimals to be truncated.

Consider following trunc example to keep two digits after the decimal.

dev=# select trunc(2.12345, 2);
 trunc
-------
  2.12
(1 row)

Amazon Redshift Date_Trunc Function

The DATE_TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, week, or month. DATE_TRUNC returns the first day of the specified year, the first day of the specified month, or the Monday of the specified week.

Redshift Date_Trunc Function Syntax

Following is the date_trunc syntax

DATE_TRUNC('datepart', timestamp)

Where, Datepart can be,

Datepart or Timepart	Abbreviations
millennium, millennia	mil, mils
century, centuries	c, cent, cents
decade, decades	dec, decs
epoch	epoch
year, years	y, yr, yrs
quarter, quarters	qtr, qtrs
month, months	mon, mons
week, weeks	w
day of week	dayofweek, dow, dw, weekday. Returns an integer from 0–6, starting with Sunday.
day of year	dayofyear, doy, dy, yearday.
day, days	d
hour, hours	h, hr, hrs
minute, minutes	m, min, mins
second, seconds	s, sec, secs
millisecond, milliseconds	ms, msec, msecs, msecond, mseconds, millisec, millisecs, millisecon
microsecond, microseconds	microsec, microsecs, microsecond, usecond, useconds, us, usec, usecs
timezone, timezone_hour, timezone_minute	Time zone parts.

Note that, date_part options are same as option available in Redshift Extract function.

Redshift Date_Trunc Function Examples

Consider following example to return to the first day of the month.

dev=# select sysdate;
         timestamp
----------------------------
 2020-09-15 14:05:59.076525
(1 row)

dev=# select date_trunc('month', sysdate);
     date_trunc
---------------------
 2020-09-01 00:00:00
(1 row)

Difference Between Redshift Trunc and Date_Trunc Functions

There are few differences between the trunc and date trunc functions, such as,

Trunc Function	Date_trunc Function
Trunc function works on both date and numeric types	date_trunc works only on the date type
Trunc function can truncate only time part	You can truncate date part such as hour, weeks, or months.
You cannot specify the date part	date_trunc requires date part. You can specify the date part such as hour, week, or month.