|[persistent] UTCTime and time zones||Adam Bergmark||9/16/13 8:46 AM|
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.
|Re: [Yesod] [persistent] UTCTime and time zones||Greg Weber||9/16/13 11:03 AM|
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.
|Re: [Yesod] [persistent] UTCTime and time zones||Adam Bergmark||9/17/13 9:12 AM|
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.
|Re: [Yesod] [persistent] UTCTime and time zones||Michael Snoyman||9/17/13 10:13 AM|
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.
|Re: [Yesod] [persistent] UTCTime and time zones||Greg Weber||9/17/13 11:40 AM|
Is the problem that we support UTCTime, but you want a different data type to be supported? What data type is that?
|Re: [Yesod] [persistent] UTCTime and time zones||David Thomas||9/17/13 11:42 AM|
Sounds to me like ZonedTime.
|Re: [Yesod] [persistent] UTCTime and time zones||Greg Weber||9/17/13 11:54 AM|
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
|Re: [Yesod] [persistent] UTCTime and time zones||Erik Hesselink||9/17/13 12:34 PM|
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.
|Re: [Yesod] [persistent] UTCTime and time zones||Michael Snoyman||9/17/13 9:20 PM|
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.