[persistent] UTCTime and time zones

[Adam Bergmark]

I noticed that persistent doesn't allow postgres' `timestamp with time zone` to be converted to UTCTime. Is this a design choice?

We want to store something with a time zone, but then read it as UTC. That way we don't have to care about time zone conversions at all since it's always possible to convert it to UTC on retrieval if we want to (and usually we do). If we on the other hand insert it as `timestamp without time zone` there is no way of knowing if the conversion was done correctly, and if we retrieve it as ZonedTime we would need to do the conversions explicitly.

Regards,

Adam

[Greg Weber]

A roundtrip through persistent should not change a Haskell value, so I think this works as designed. I don't understand what you mean by "no way of knowing if the conversion was done correctly", if you explain that more maybe we will have a better answer for you.

--
You received this message because you are subscribed to the Google Groups "Yesod Web Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Adam Bergmark]

I seem to have messed up a bit  in my explanation :)

A round trip wouldn't change the value.

Having times stored `without timezone` in the db means a loss of information, so it doesn't make sense to me that you can then read it as UTCTime since you don't know if it really is. If it's with timezone then persistent could just convert it to UTCTime. The model also says UTCTime so we have to do this conversion before insertion (that's fine), or we could use a non-persistent client/different persistent model and insert any time zone we want. We also wouldn't have to migrate our existing tables.

[Michael Snoyman]

As I understand it, "timestamp with timezone" is essentially a UTCTime plus a timezone. This would be useful when you're going to say something like "schedule a meeting for July 5 at 2pm, PST, and schedule another meeting every week at the same time." It's not sufficient to just take the UTCTime and add 7 days to it, because it needs to be adjusted for daylight savings time. "timestamp without timezone", on the other hand, is just the UTCTime.

Not sure if that clears things up at all. I know that I had a mistaken assumption about the distinction between these two types previously.

[Greg Weber]

Is the problem that we support UTCTime, but you want a different data type to be supported? What data type is that?

[David Thomas]

Sounds to me like ZonedTime.

[Greg Weber]

We do support ZonedTime, but I think the issue is that ZonedTime is a LocalTime + TimeZone, but perhaps what is desired here is UTCTime + TimeZone

[Erik Hesselink]

I think this is wrong. A UTCTime has a timezone: UTC. This is witnessed by the value 'utc :: TimeZone' from the time package. You can convert a UTCTime to a ZonedTime in any time zone, and back again. This means that a 'timestamp with time zone' can safely be converted to either a ZonedTime, or a UTCTime, and back again. a 'timestamp' (without time zone) is only convertible to a LocalTime, since you have no information about the timezone.

If you look at the postgresql-simple package (in the FromField instances) or the HDBC package (in the Convertible instances) this is precisely the approach they take: zoned timestamps to either UTCTime or ZonedTime, and unzoned timestamps to LocalTime.

Regards,

Erik

[Michael Snoyman]

You're right, I for some reason though that PostgreSQL required that the default timezone be UTC, but upon further research, that's not the case.

Nonetheless, converting "timestamp with timezone" to UTCTime would be throwing away information on the timezone, which is why we don't do that conversion by default.