Search code examples
postgresqldatedatetimetimezonelocaltime

PostgreSQL - extracting date ranges in local time?

CURRENT SITUATION:

  • I have a table of wildfire incidents with a timestamp with time zone (timestamptz) field to track when the observation occurred.
  • Everything in the data creation process is in UTC: the incoming data from the source, the app server that inserts the data, the insert python code (appends a "Z" to the time), and the database server are all in UTC.
  • The incidents' geographic extent spans several time zones in the US, Canada, and Mexico.

PROBLEM:

  • I've been querying on a day's worth of data in UTC time, but need to extract out data relative to local time. The midnight to midnight range will be different in each time zone.
  • My use case now is one day, but I was asked to consider arbitrary time ranges. E.g.: find all incidents in the hottest part of the day (say 10:00 to 18:00) local time.
  • This table is quite large and I have an index on the timestamptz field right now. Any changes I make will need to work with an index.
  • Account for daylight saving time.

I have a method to get the time zone for each record, so I don't need help with that.

I created a test table for this with a timestamptz field ts_with and a varchar field for the time zone tz. The following query returns what I want, so I feel like I'm making progress. 

SELECT 
    name, test_tz.ts_with, test_tz.tz, 
    TIMEZONE(test_tz.tz, test_tz.ts_with) as timezone_with
FROM fire_info.test_tz
WHERE TIMEZONE(test_tz.tz, test_tz.ts_with) BETWEEN 
    '2018-08-07 00:00:00' AND '2018-08-07 23:59:59';

QUESTIONS:

  • Will this use my index? I'm thinking the timezone function will avoid it. Any solution for that? I'm considering adding another condition to the where clause that selects on timestamptz buffered by a day on either side. That would use the index and then the timezone function isn't sorting through too much data (~6k records per day during fire season). Would PG figure that out?
  • The timezone function is giving me DST offsets (e.g.: Denver is currently UTC-06). I assume I'll get standard time after DST ends. If I run a query in December for data in August, will it apply standard time or DST?

thanks!!!

Solution

  • The way you wrote the query, it cannot use an index on ts_with.

    To use an index, the condition would have to be of the form ts_with <operator> <constant>, and there is no way to rewrite the query in that fashion.

    So you should create a second index on timezone(test_tz.tz, test_tz.ts_with).