[Fixed]-Query local time

by

0đź‘Ť

âś…

Postgres supports converting a time to a local time using the at time zone syntax. For example to find the current time in New Zealand:

select (current_timestamp at time zone 'NZDT')::time;

You could use this to select shops that are open at 10:00:

where   ('10:00'::time at time zone time_zone)::time
        between opens and closes

Where time_zone is the timezone for the shop, opens the time it opens, and closes the time it closes. Full example at regtester.com.

👤Andomar

1đź‘Ť

Cool question. Continuing Andomar’s answer above, assuming you are handling a few timezones with “unexpected” daylight saving date ranges, one option would would be:

  • Save the timezone in a CharField, and opens and closes in a TimeField:

    class Shop(models.Model):
    tz = models.CharField(max_length=200)
    opens = models.TimeField()
    closes = models.TimeField()

Shop.objects.create(opens="8:00", closes="19:00", tz="Europe/Moscow")
Shop.objects.create(opens="8:00", closes="19:00", tz="Europe/Berlin")
Shop.objects.create(opens="8:00", closes="19:00", tz="UTC")
Shop.objects.create(opens="8:00", closes="19:00", tz="Asia/Jerusalem")
Shop.objects.create(opens="8:00", closes="19:00", tz="Europe/London")
Shop.objects.create(opens="8:00", closes="19:00", tz="Europe/Copenhagen")

  • Calculate “now” as UTC:

    now_utc = "10:30"

  • Annotate and filter your queryset with RawSQL:

    qs = Shop.objects.annotate(is_open=RawSQL("(%s::time at time zone tz)::time between opens and closes", (now_utc,))).filter(is_open=True)

Another solution would be to query the db for each timezone:

# pseudocode
for tz in all_timezones:
    now_local = convert_to_timezone(now, tz) # beware - this might fail when DST is currently changing!
    shops = Shop.objects.filter(tz=tz, opens__lte=now_local, closes__gte=now_local)

If you index_together fields (tz, opens, closes), the query should utilize the index. However, this does not mean your query will be faster.

Keep in mind you will have to keep your opening hours that surrounds midnight in two records “22:00”-“00:00” and “00:00”-“03:00” instead of “22:00”-“03:00”.

👤Udi

Leave a comment