1

I have a query where I compute various statistics and metrics, and group by dates in the last week. The times do not have a time zone associated with them, but are displayed in UTC so I want to convert them to Pacific time first. When I get dates that are within 7 days of the current date, it only gives the 6 groups that represent the last 6 days. How come this is the case and how do I fix this? Thanks!

select
 date_trunc('day', time1 AT TIME ZONE 'US/Pacific') as day,
 ...
where is_displayed = 'yes'
 date_trunc('day', time1 AT TIME ZONE 'US/Pacific') >= now() - interval '7 days'
group by day;
Jane Sully
  • 3,137
  • 10
  • 48
  • 87
  • If dates are stored in UTC I suggest you do all your working in UTC and then convert to the current time zone in the select only? – Caius Jard Dec 17 '18 at 06:33
  • Have you tried removing the group, and putting additional columns representing the raw data. selecting your manipulated data alongside the raw data in the db is usually the best way of working out why the raw data doesn't become manipulated in the way you expect.. – Caius Jard Dec 17 '18 at 06:48
  • Right now it feels like this is a time adjustment issue, like the program that stored the data in the db put it in as the local time but without the time zone rather than UTC with time zone, so the db is treating the local time as UTC, and when minus-sixing it to get it to PST and determine if it's in range it's falling out of range when it shouldn't be. – Caius Jard Dec 17 '18 at 06:51
  • What is the data type of `time1`? – Laurenz Albe Dec 17 '18 at 07:05
  • Try using `current_date` instead of `now()`. – Gordon Linoff Dec 17 '18 at 12:42

0 Answers0