0

Why is the August 1996 data being classified in July? I am using the below query to calculate monthly results but the query doesn't give correct results. If ts is 1st day of the month it classifies the record in the previous month.

SELECT
        ts,
         date_trunc(
    'month',
    (
      ts :: timestamptz AT TIME ZONE 'UTC'
    )) as month,
    activity,
    revenue_impact
 from
          activity_stream
        where
          activity = 'completed_order'
 order by
      month

Output I am getting

Ansa Dhedhi
  • 29
  • 5
  • 3
    It is returning the month of a UTC date. On the first day of the month in your local time zone, it may be the previous day in UTC time. See this link: https://stackoverflow.com/questions/36341060/convert-a-utc-timezone-in-postgresql-to-est-local-time – John D Jul 06 '21 at 20:37
  • 2
    To understand this, just look at the output of `ts::timestamptz AT TIME ZONE 'UTC'` (without the `date_trunc`) –  Jul 06 '21 at 20:54
  • It comes down to whether you want the month as determined by your local time or UTC? – Adrian Klaver Jul 06 '21 at 21:16
  • @a_horse_with_no_name. i tried this solution. turns out it is the 5 hour difference in time zones which is the main problem here . how to resolve? – Ansa Dhedhi Jul 07 '21 at 19:25
  • To come with an answer you have to tell us what time zone you want the timestamps to be anchored to. `select '1996-08-01 00:00:00+5'::timestamptz at time zone 'UTC';` is going to resolve to `1996-07-31 19:00:00` in UTC and that is in July and correct. – Adrian Klaver Jul 07 '21 at 21:56

0 Answers0