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
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;
"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?
The DATE_TRUNC Function – Amazon Redshift takes timestamp as input and provides a timestamp as output:
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
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).