#Annotating a datetime in another timezone using an F object

29 messages · Page 1 of 1 (latest)

eager marten
#

I know how to do this by providing a tzinfo object to Trunc. The difference here is I want to use the timezone in an F object, and the timezone I want to use will change based on the row.

Any ideas?

neon pulsar
#

can you explain further? Like, give an example?

eager marten
#

Sure,

Here's an example of how I've annotated datetimes in another timezone in the past:

tz = pytz.timezone("America/Vancouver")
User.objects.annotate(created_local=Trunc("created_utc", "second", tzinfo=tz))

Pretty easy. The difference now is I want the tzinfo to be from a related row of the user. It will be stored as "Europe/Zurich" for example (not sure what this format is called)

So I want to do something like:

User.objects.annotate(created_local=Trunc("created_utc", "second", tzinfo=F("address__timezone")))

but obviously this doesnt work because I can't use an F object there, and also my field isn't a tzinfo object!

eager marten
neon pulsar
#

I can't think of how to do this, and it makes me itchy. I prefer to always deal with UTC only, with the sole exception being when I read times from, or write times to, something external to django. That way I don't have to think about this sort of thing.

eager marten
#

Yes it's certainly not ideal, but I need to query for a bunch of appointments across multiple timezones that happened on a specific local date!

neon pulsar
#

well

#

so your database holds appointments? And their timestamps have various timezones?

eager marten
#

No they are all in UTC

neon pulsar
#

oh good

eager marten
#

But I need to fetch only the ones that are on Jan 14th local time, for example

neon pulsar
#

why not just convert the "specifc local date" to UTC, and then live the life of Riley?

eager marten
#

Well my local date is just a date, not a date time so it's timezone naive

#

You're right I guess I could go that way. Do some kind of datetime.combine for the start and end times of the date, then check if my appointments are in that range

#

That's a good idea, thanks!

neon pulsar
#

seems simpler

#

I am obsessed with timezones for some reason ! 🙂

eager marten
#

Sometimes they break my brain 😆

eager marten
#

I remembered why this doesn't really work either

I can't just convert my local date to UTC because its timezone depends on the timezone of the appointment row in the queryset. It's just a naive datetime until it has more context.

It's all good though, I will find another way!

thick marten
#

What SQL backend are you using? Does it support any special functions you might be able to use with Func to then be able to use address__timezone as part of the query?

eager marten
#

Yeah I've never dug into how to use Func but I'm guessing it's going to involve that

#

Using postgres

thick marten
#

You might be able to use RawSQL as well. Something like .annotate(created_local=RawSQL("created_utc AT TIME ZONE '%', params=(F('address__timezone')).

To go the Func route, this might work. Both are untested.

from django.db.models import Func

MyModel.objects.annotate(
    created_local=Func(F('created_utc'), Value('AT TIME ZONE'), F('address__timezone'), output_field=DateTimeField())
)
eager marten
#

I was able to get this to work with a static timezone (ie/ "America/Vancouver"), but it wouldn't work when I tried to use a field for the time zone.

Honestly this is kind of code smell anyway. I'm going to find a better way. I appreciate all the input

lone flame
#

What I typically do is figure out which sql function or expression I need based on my database, then search for that in django, and if it doesn't exist, create my own expression or func to do that.

cloud sandal
eager marten
#

I never bothered. Just found a way to not write that specific query.

neon pulsar
eager marten
#

Well it's a date object, so it can't be timezone aware