Updated May 12, 2023
Introduction to Redshift date_trunc
Redshift date_trunc function is used to truncate a timestamp or interval based on a specified date part, e.g. month, week or hour, and it will return the truncated timestamp or interval with the level of precision. The date_trunc function in redshift is specifically used to truncate the specified precision. For example, if we want to truncate the date and time value to the nearest hour or week, it is possible to truncate using the date_trunc function. The date_trunc function contains the two input parameters, i.e. datepart and timestamp, and the return type of date_trunc function in redshift is a timestamp.
Syntax of Redshift date_trunc
Below is the syntax of the date_trunc function in redshift:
Select date_trunc (‘datepart’, column_name), column_name, …., column_nameN from table_name where condition group by clause order by clause
Date_trunc (datepart, timestamp)
Date_trunc (interval, column_name)
date_trunc (interval, timestamp)
Parameters:
- date_trunc: This function in redshift is similar to trunc, which was used for numbers. We are using this function for date and time values. If suppose we want to remove unwanted details from the date and time, then we have using the date_trunc function in redshift.
- datepart: Using this parameter, we have to truncate the specified timestamp value. This is an essential parameter of the date_trunc function in redshift.
- column name: We are using column name to truncate the specified timestamp value from the date column. We have to specify the column name which data type contains the date value.
- timestamp: Using the date_trunc function, we can also use the timestamp value, which was used to convert the timestamp column into the timestamp. This parameter is also converting the expression into the timestamp format.
- interval: This parameter is nothing but the interval argument, which was we have to pass with the date_trunc function in redshift.
How date_trunc Works in Redshift?
Below is the field or interval argument of precision, which was used to truncate the date using the date_trunc function in redshift.
- Century
- Year
- Epoch
- Timezone
- Timezone hour
- Timezone minute
- Decade
- Millennium
- Quarter
- Month
- Week
- Minute
- Hour
- Day
- Milliseconds
- Seconds
- Microseconds
Basically, we can say that the function will work on only the date type column or value. Using the function, we can truncate the hour, weeks, months, year, day, seconds, minute etc. To use the date_trunc function in redshift, it will require the date part. We can specify the date part as a date type column name or weeks, months, year, day, seconds, minute. We cannot use the date_trunc function on a string or numeric data type columns. To use the date_trunc function, we need the data type of column as date type format.
The below example shows that to use the date_trunc function, we need data type of column as date type format.
In the below example, we have used column name as salesid; this column contains the data type as integer. So at the time of executing the below query, it will issue the error that the function will not be matching the argument types.
Code:
SELECT date_trunc ('year', salesid) from sales group by date_trunc ('year', salesid);
Output:
To use the date_trunc function, we need the data type of column as date type format.
We can also use the date_trunc function with a group by and order by clauses to retrieve the data as per order or per clause, we used in our query. If we want to truncate the timestamp value from minute, then we need to pass the minute argument with the date_trunc function. If we truncate the part of date like microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, a millennium from the specific date, then we need to pass the particular parameter in the first argument using date_trunc function in redshift. There are mainly two parameters which we have passing with the date_trunc function, i.e. interval and timestamp in redshift.
The function will return the timestamp value. We can also use the now function with the date_trunc function. After using the now function with the date_trunc function will pass the current date from the system. After passing the current date and time, it will truncate the value which was we have to pass in our query. Therefore, after retrieving the date and time value from the column, we do not need to extract the timestamp value to use the date_trunc function on the specified column.
Examples of Redshift date_trunc
Given below are the examples of Redshift date_trunc:
Example #1 – With now function.
- The below example shows that date_trunc with now function is as follows. After using the now function with date_trunc will truncate the current date value from the specified parameter value.
- We have used the argument as an hour, so it will truncating the minutes from the current date.
Code:
SELECT DATE_TRUNC ('hour', now ());
Output:
Example #2 – Function with parameter as a column name.
- The below example shows that the function with parameter as a column name.
- We have used column name as saletime form sales table. We have used datepart parameter as a month.
- Also, we have used group by clause with the date_trunc function.
Code:
SELECT DATE_TRUNC ('month', saletime) from sales group by date_trunc ('month', saletime) limit 5;
Output:
Example #3 – Function with a timestamp.
- The below example shows that the function with a timestamp. After using timestamp with date_trunc, it will truncates the datepart value from the specified timestamp.
- We have passing the datepart argument as day.
Code:
SELECT DATE_TRUNC ('day', TIMESTAMP '2021-05-01 06:55:55');
Output:
Example #4 – Function with datepart parameter as a year.
- The below example shows that the function with the datepart parameter as a year.
- We have used the datepart argument as a year.
Code:
SELECT DATE_TRUNC (‘year’, TIMESTAMP '2021-05-01 06:55:55');
Output:
Example #5 – Function with datepart parameter as a minute.
- The below example shows that the function with the datepart parameter as a year.
- We have used the datepart argument as a minute.
Code:
SELECT DATE_TRUNC (‘minute’, TIMESTAMP '2021-05-01 06:55:55');
Output:
Conclusion
The function will truncate the expression of timestamp or lateral of the datepart which we have used in our query. The input parameter of the date_trunc function in redshift is datepart and timestamp. The return type of date_trunc function in redshift is a timestamp.
Recommended Articles
This is a guide to Redshift date_trunc. Here we discuss the introduction, how date_trunc works in Redshift? and examples, respectively. You may also have a look at the following articles to learn more –
