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?

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).