Question 89785 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres DATE_TRUNC not behaving as expected – incorrect data showing – Postgresql

I have the following records that were created_at:

2022-11-13 14:24:02.651809
2022-11-13 15:00:43.878400
2022-11-13 16:33:18.710288
2022-11-13 18:22:59.220246
2022-11-13 18:50:59.719803
2022-11-15 20:33:19.099264
2022-11-16 15:42:51.285751
2022-11-16 15:46:28.196001
2022-11-16 16:06:31.873187
2022-11-16 16:47:32.071435
2022-11-16 19:05:38.288788

When I run:

SELECT
DATE_TRUNC('day', CAST(forms.created_at AS DATE) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' as "created_at",
count('created_at') AS total_forms
FROM forms
WHERE
(forms.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'EST' BETWEEN '2022-11-13 00:00:00'::timestamp AND '2022-11-17 00:00:00'::timestamp
GROUP BY 1
ORDER BY created_at asc;

It returns the following:

2022-11-13 05:00:00.000000 +00:00,5
2022-11-16 05:00:00.000000 +00:00,5
2022-11-17 05:00:00.000000 +00:00,1

This is incorrect as it should read:

2022-11-13, 5
2022-11-15, 1
2022-11-16, 5

I’m not sure why it’s not showing 2022-11-15. Does anyone have any idea how I can resolve this?

I have tried using various forms of CAST

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It was actually a pretty simple fix. I had to wrap the timezone in the DATE_TRUNC. So I had to change

    DATE_TRUNC('day', CAST(forms.created_at AS DATE) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' as "created_at",

    To this

    DATE_TRUNC('day', (forms.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS "created_at",

  2. 0

    date_trunc always returns a timestamp, not a date.

    If you want just the date in the current time zone, cast to a date.

    SELECT
  cast(created_at as date) as created_at,
  count(created_at)
FROM forms
group by 1
ORDER BY created_at asc;

    If you want the date in a particular time zone, date_trunc with that time zone then cast to a date.

    SELECT
  cast(date_trunc('day', created_at, 'Australia/Sydney') as date) as created_at,
  count(created_at)
FROM forms
group by 1
ORDER BY created_at asc;

    Demonstration.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search