Skip to content
Advertisement

Date_Trunc not function working as expected

I am trying to use the Date_Trunc for MONTH function in a SQL statement but somehow it is not working for me. I am trying to pull entries which happen after April 1st, 2019. The raw date format from the Redshift database is this format which I am trying to group into month/year buckets: 2019-04-08T00:13:20.000Z

Input

SELECT
    client_id as user_id,
    session_utc as job_date --(format:2019-04-08T00:13:20.000Z)
    FROM  table1 as hits
    WHERE job_date >= DATE_TRUNC('month', 2019-04-01) 
    group by 1,2;

Output

"ERROR: function date_trunc("unknown", integer) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts."

What am I doing wrong?

Advertisement

Answer

The DATE_TRUNC Function – Amazon Redshift takes timestamp as input and provides a timestamp as output:

DATE_TRUNC('datepart', timestamp)

For example:

SELECT DATE_TRUNC('month', '2019-05-07'::timestamp)

2019-05-01 00:00:00

Therefore, your line should read:

WHERE job_date >= DATE_TRUNC('month', '2019-04-01'::timestamp) 

If you wish to have the output as a date, append ::date:

SELECT DATE_TRUNC('month', '2019-05-07'::timestamp)::date

2019-05-01

Also, note that the date converts into a timestamp as at midnight. This can cause a difference for some comparisons. For example:

'2019-05-07 03:03:31.389324+00'::timestamp > '2019-05-07'::timestamp

will evaluate as True because it is comparing to midnight at the start of the day. This is different to comparing two dates (without timestamps).

10 People found this is helpful
Advertisement