UTCTime/LocalTime vs timestamptz/timestamp

[lpsmith]

When you are dealing with binary formats, postgresql timestamp values (with or without time zone) just acts as a store of 8-byte values, independent of timezones:

$ ghci-7.6.3
GHCi, version 7.6.3: http://www.haskell.org/ghc/  :? for help
> import Database.PostgreSQL.LibPQ 
> let showResult m = m >>= maybe (return Nothing) (\r -> getvalue r 0 0)
> c <- connectdb ""

> showResult $ exec c "show integer_datetimes"
Just "on"

> exec c "set timezone to 'utc'"
Just (Result 0x00007eff8c0092f0)

> showResult $ execParams c "select $1" [Just (Oid 1184, "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL", Binary)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1" [Just (Oid 1184, "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL", Binary)] Text
Just "2000-01-01 00:00:00+00"

> showResult $ execParams c "select $1" [Just (Oid 1184, "2000-01-01 00:00:00+00", Text)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1" [Just (Oid 1184, "1999-12-31 19:00:00-05", Text)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1 = $2" [Just (Oid 1184, "1999-12-31 19:00:00-05", Text), Just (Oid 1184, "2000-01-01 00:00:00+00", Text)] Text
Just "t"


> exec c "set timezone to 'America/New_York'"
Just (Result 0x00007eff940083b0)

> showResult $ execParams c "select $1" [Just (Oid 1184, "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL", Binary)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1" [Just (Oid 1184, "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL", Binary)] Text
Just "1999-12-31 19:00:00-05"

> showResult $ execParams c "select $1" [Just (Oid 1184, "2000-01-01 00:00:00+00", Text)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1" [Just (Oid 1184, "1999-12-31 19:00:00-05", Text)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

> showResult $ execParams c "select $1 = $2" [Just (Oid 1184, "1999-12-31 19:00:00-05", Text), Just (Oid 1184, "2000-01-01 00:00:00+00", Text)] Text
Just "t"

However, there's still a semantics to each of the types, and hasql-postgres has the semantics precisely wrong:

> showResult $ execParams c "select $1" [Just (Oid 1114, "1999-12-31 19:00:00-05", Text)] Text
Just "1999-12-31 19:00:00"

> showResult $ execParams c "select $1" [Just (Oid 1114, "1999-12-31 19:00:00-05", Text)] Binary
Just "\255\255\255\251\207\GS\204\NUL"

> showResult $ execParams c "select $1 = $2" [Just (Oid 1114, "1999-12-31 19:00:00-05", Text), Just (Oid 1114, "2000-01-01 00:00:00+00", Text)] Text
Just "f"

Yeah, that doesn't look like a UTCTime to me. When a backend is using the integer_datetime format, a timestamptz is an 8-byte signed integer of microseconds from 2000-01-01 00:00:00+00, whereas a timestamp is an 8-byte signed integer of microseconds from somewhere between 2000-01-01 00:00:00-24 and 2000-01-01 00:00:00+24, and this ambiguity can only be resolved in some larger context.

In short, UTCTime really should map to timestamp with time zone, whereas LocalTime really should map to timestamp. And, in the vast majority of circumstances, timestamp with time zone is the correct choice, and one should always include a offset from UTC when using textual formats, as postgresql-simple's ToField instances for UTCTime and ZonedTime do.

[nikita-volkov]

Thanks for your input.

As you know, OID value of 1114 means timestamp. That is a timestamp without timezone. Yet you're passing a string with a timezone information expecting Postgres to take it into accout. However what Postgres actually does is it just silently ignores the timezone information. It's easy to prove:

λ>showResult $ execParams c "select $1" [Just (Oid 1114, "1999-12-31 19:00:00-05", Text)] Binary
Just "\255\255\255\251\207\GS\204\NUL"
λ>showResult $ execParams c "select $1" [Just (Oid 1114, "1999-12-31 19:00:00", Text)] Binary
Just "\255\255\255\251\207\GS\204\NUL"

The following statement is wrong as well:

When a backend is using the integer_datetime format, a timestamptz is an 8-byte signed integer of microseconds from 2000-01-01 00:00:00+00, whereas a timestamp is an 8-byte signed integer of microseconds from somewhere between 2000-01-01 00:00:00-24 to 2000-01-01 00:00:00+24

The timestamp value of "2000-01-01 00:00:00" is encoded to zero as well:

λ>showResult $ execParams c "select $1" [Just (Oid 1114, "2000-01-01 00:00:00", Text)] Binary
Just "\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL"

Your confusion is understandable however, since Postgres authors have implanted a bomb in their timestamptz implementation. I've actually explained it in the documentation. So trust me, the choice of how timestamps are mapped in the library is a result of a research, not at all the first thing that came to my mind.

[lpsmith]

The point is, 2000-01-01 00:00:00 is not, in and of itself, an unambiguous point in time without a larger context which you can't assume is UTC. ISO 8601:2004(E), Section B.1.3, gives some concrete examples:

Combinations of calendar date and local time:   1985-04-12T10:15:30
Combinations of ordinal date and UTC of day:   1985-102T23:50:30Z

On the other hand, specifying an offset, even if it's '+00' or 'Z', specifies an unambiguous point in time without the need for further context.

Let's examine if your theory that timestamp represents UTC holds any water, according to postgresql:

$ psql
psql (9.3.5)
Type "help" for help.

lpsmith=> set timezone to 'UTC';
SET
lpsmith=> select ('2000-01-01 00:00:00'::timestamp) at time zone 'UTC';
        timezone        
------------------------
 2000-01-01 00:00:00+00
(1 row)

lpsmith=> select ('2000-01-01 00:00:00'::timestamp) at time zone 'EST';
        timezone        
------------------------
 2000-01-01 05:00:00+00
(1 row)

lpsmith=> set timezone to 'EST';
SET
lpsmith=> select ('2000-01-01 00:00:00'::timestamp) at time zone 'UTC';
        timezone        
------------------------
 1999-12-31 19:00:00-05
(1 row)

lpsmith=> select ('2000-01-01 00:00:00'::timestamp) at time zone 'EST';
        timezone        
------------------------
 2000-01-01 00:00:00-05
(1 row)

Now, your documentation suggests that you convert to local time yourself. (Actually, you have to, with binary formats, in which case all timestamptzs are always returned in UTC.) How exactly would you do that, in the case of New York? What about Indianapolis?

[nikita-volkov]

The thing is, a timestamp value on its own without any explicit or implied timezone does not make sense. When you say "I'll meet you at noon", you imply 12 am local time zone. Same way, when a server located in the -05 timezone uses an unzoned value 2000-01-01 00:00:00, it is implying its own local time, hence the actual value is 2000-01-01 00:00:00-05, i.e. 2000-01-01 05:00:00 UTC. When another server located in the +3 timezone reaches this value in the DB, it gets a local time value of 2000-01-01 08:00:00. Turns out, this is exactly how Postgres's timestamptz behaves. IOW, it encodes local time, which in Haskell is represented by, you know, LocalTime.

On the other hand, there can't be any time zone information associated with UTC, because it's always the same, no matter where you are. Hence adding a timezone to it does not make sense either. That is why it's encoded as timestamp.

As you see both cases are resolved unambiguously and cover all proper use case scenarios. The database server always stores an absolute time and its client servers are always refering to the same point in time despite their timezones.

[snoyberg]

I'm going to comment here with the caveat that I've been told my usage of PostgreSQL's datatypes was wrong, only to change it and have a different group of people tell me that the new way is wrong. So I'm clearly not an expert on the topic, but that seems to be because there's a lot of confusion.

I think @nikita-volkov's approach works correctly if you have control over all programs accessing the database. Then you can establish the convention that all timestamps are stored at UTC. The problem is that this isn't the meaning intended for this field by the PostgreSQL authors/SQL spec authors... though it's unclear what the exact meaning of the field is supposed to be.

So theoretically, following this approach, someone could come along and assume that the timestamp value is stored in CET or IST or something else, and everything breaks. Using timestamptz solves this, because the client will (theoretically) get the data in the format they're asking for, either local time or UTC time.

What I'm not clear on @nikita-volkov is your concern about using timestamptz.

[lpsmith]

@snoyberg, there's a lot of confusion about time, period. Historically, computer software tended to be ridiculously bad at time. We've come a long way. On a technical level, PostgreSQL is pretty far ahead of where hackage is, time-wise. The venerable time library has some advantages over stock postgres, but also completely misses the mark with regard to timezone handling, especially by conflating timezones with offsets. Haskell would do well to have a high-performance time library that has a proper concept of what a timezone is and high-performance, relatively pure access to the IANA timezone database.

Postgresql-simple does not have an opinion on the context surrounding the use of timestamp. If you want to treat it as UTC, you certainly can.

@nikita-volkov, you definitely have a few misconceptions here. First of all, the OS time zone configured on the postgresql server is not necessarily relevant: the timezone is a GUC variable, which is local to the connection and can be initialized based on global, per database, and/or per user settings. (IIUC "localtime" is the default global configuration and normally signifies the server's time zone.) In addition, it can be modified by client applications at any time for their entire session (connection), or just for the current transaction. Server-side functions can also modify the variable for their own local execution context.

Second of all, UTC is most definitely a timezone. It's the ur-timezone, in fact. -05 and +03 are offsets, not timezones. However, Europe/Moscow and Europe/Volgograd are timezones, and they are indeed different timezones. Both cities are on the same standard time. So what is the difference between a standard time and a time zone?

Regarding your proposed scenario, yes, treating 2000-01-01 00:00:00 as a timestamptz has a behavior that may or may not be intended. If postgres was less DWIM in this scenario, timestamp would strictly prohibit offsets, and timestamptz would strictly require them. And it's true, timestamp with time zone is a bit of a misnomer, but IIRC it was done because the SQL standard requires that type, but the standard is very much naive about time zones in the same way that the time package is. So postgresql is trying to be standards compliant and offer reasonable behavior.

The way I think of it, is that if you write '2000-01-01 00:00:00'::timestamptz there's an implied ('2000-01-01 00:00:00'::timestamp) at time zone (show timezone), ignoring the fact that using show timestamp as an expression is not syntactically legal. If that wasn't intended, then that's a bug in the client program, not an argument against timestamptz. And with the plethora of options to set the timezone for that client program, without modifying the client program itself, chances are pretty good that this issue could be worked around by a competent sysadmin.

But, when the second program retrieves the timestamptz in a textual format, it will receive 2000-01-01 08:00:00+03, i.e. 2000-01-01 05:00:00+00. It will most certainly not receive 2000-01-01 08:00:00. But again, this is invisible to the client that is using binary results: timestamptz (when integer_datetimes is on) is offset from 2000-01-01 00:00:00+00, so you always get back UTC. There simply is no room in the format for transmitting a local offset, and the binary format is not modified into a local time.

On the other hand, timestamp is offset from 2000-01-01 00:00:00, which according to ISO 8601, is a local time. That local time may be UTC, it may be some other timezone, or it may even be dependent on the row. (In which case comparisons between timestamps are only some arbitrary ordering, not an ordering in time.)

To be pendantic, what is 2000-01-01 00:00:00+00 in local Moscow time? It's 3:00 am, right?

$ psql
psql (9.3.5)
Type "help" for help.

lpsmith=> set timezone to 'Europe/Moscow';
SET
lpsmith=> select ('2000-01-01 00:00:00+00'::timestamptz);
      timestamptz       
------------------------
 2000-01-01 03:00:00+03
(1 row)

lpsmith=> select ('2000-01-01 00:00:00+00'::timestamptz) at time zone 'Europe/Moscow';
      timezone       
---------------------
 2000-01-01 03:00:00
(1 row)

Note that at time zone turns timestamptz into timestamp and vice-versa, converting UTCTime into LocalTime and back. So, if timestamp represented UTC, the following expression must return the same result:

lpsmith=> select ('2000-01-01 00:00:00'::timestamp) at time zone 'Europe/Moscow';
        timezone        
------------------------
 2000-01-01 00:00:00+03
(1 row)

But it doesn't! It returns 1999-12-31 21:00:00+00! This very much implies that timestamp is local time whereas timestamptz is UTC!

[nikita-volkov]

@lpsmith

this is invisible to the client that is using binary results: timestamptz (when integer_datetimes is on) is offset from '2000-01-01 00:00:00+00', so you always get back UTC.

That's the issue I somehow completely missed out on, while decoding the binary format specs from the "libpqtypes" library, and I actually am addressing it right now. I was originally convinced that the binary format encoded an offset value the same way the text format did.

This news completely changes my perspective, since I don't believe that the library should do any transformation on the essence of the data coming from db. So yes, since, as it turns out, Postgres is talking in UTC time in binary format of timestamptz values, I guess I'll have to switch to UTCTime mapping after all.

With that kind of inconsistencies, which I had to deal with in almost every tiny detail and the freakin lack of any kind of a spec or a documentation I'll say anything but "Kudos" to Postgres authors. Really I couldn't even imagine the owners of such a huge product to be irresponsible on such a scale when I took on this project.

While we're at it, where did you get your information about the binary format?

---

@snoyberg

there's a lot of confusion

it's unclear what the exact meaning of the field is supposed to be

That's both the bottom line and the root of all evil here. The standards are too vague and leave the question of interpretation open, hence we get lots of points of view.

What I'm not clear on @nikita-volkov is your concern about using timestamptz.

My concern is that Postgres' interpretation of timestamptz only extends the confusion introduced by the SQL standards. People expect it to behave the same way as timetz, i.e. to actually store the time and timezone information, however it does a fundamentally different thing.

---

@lpsmith, @snoyberg, thank you guys for your inputs. It was a nice discussion and I think we've reached an understanding. I'm revisiting the API right now and most probably will remap UTCTime to timestamptz and LocalTime to timestamp.

[lpsmith]

I got my information through experimentation with PostgreSQL, google searches, wikipedia, some irc sessions on #postgresql, reading a few primary sources (esp the IANA timezone database and ISO 8601:2004), a little bit of source code diving, and keeping a clear mental model of the semantics of time and postgresql's physical format in my head. And probably a couple of things I've since forgotten about.

In short, getting time right was important to me from the outset, and I quickly resolved to always know a little bit more about time than I really needed to try to avoid ever being caught flat-footed by these issues. (And I was caught flatfooted on time, a little bit, in the early days.) And when I go back and read something I wrote about time a while ago, my ignorance then is pretty obvious to me now. There's always more to learn about time, if you care.

I was pretty sure that the binary format did not result in a conversion to local time, as there simply was no way it could possibly have a sensible behavior otherwise. But I didn't know for 100% until I ran the demo postgresql-libpq session in my first post in this thread.

One issue I doubt you are aware of, and is probably ok to ignore for the time being, is that the old time format is a double-floating point value representing a difference from some epoch. I'm not sure what the unit is or the epoch, though. integer_timestamps is a setting that can only be changed at compile time of the server, IIRC Debian moved to it pretty quickly but Red Hat didn't switch their repo'd version of postgres to integer_datetimes until postgresql 9.0, which IIRC is the first release that changed the default setting. (And yes, I do work with postgresql 8.1 running on CentOS, so it will affect some people.)

The problem with the floating-point format seems obvious, at least in hindsight: Right around the epoch you can represent extraordinarily small units of time, far smaller than the Planck time and thus highly unlikely to have any physical meaning, whereas you lose significant levels of accuracy far from the epoch.

And, if you want a better understanding of timezones, and in particular why the time package gets it so incredibly wrong, you may find lpsmith/postgresql-simple#69 useful. A brief sketch of my idea of how time should look is contained in lpsmith/postgresql-simple#128. You really are going to need to have efficient IANA timezone conversions for this project, eventually.

[nikita-volkov]

One issue I doubt you are aware of, and is probably ok to ignore for the time being, is that the old time format is a double-floating point value representing a difference from some epoch. I'm not sure what the unit is or the epoch, though. integer_timestamps is a setting that can only be changed at compile time of the server, IIRC Debian moved to it pretty quickly but Red Hat didn't switch their repo'd version of postgres to integer_datetimes until postgresql 9.0, which IIRC is the first release that changed the default setting. (And yes, I do work with postgresql 8.1 running on CentOS, so it will affect some people.)

Actually I'm quite aware, since I've been extracting the info from their source code base. I was just hoping that you had a reach to any kinda spec.

Oh, well, on lives the digging. I'm knee deep in it anyway...

[lpsmith]

Well, my suggestion is to ask for pointers in #postgresql. My personal experience has been that channel tends to be pretty friendly and helpful.

Honestly, from talks there and elsewhere it seems that, especially if you use a lot of the more exotic types, textual formats are a lot easier way to go, as the binary formats are often not all that well specified, documented, or stable. A lot of applications that do use binary formats only use them for a few performance-critical types.

Unfortunately, binary results are an all-or-nothing proposition with libpq, although it is possible to request specific formats for individual columns at the protocol level. So the standard workaround for libpq-based applications is to manually cast columns you can't deal with in binary to text. This is ugly on several levels, not to mention that it doesn't mesh well with the *-simple view of typechecking.

I've been thinking about supporting protocol-level parameters, binary parameters, and binary results in postgresql-simple since near the outset, and I've long been aware that's a ~2x speedup for certain workloads. (Possibly a bit more with timestamp-heavy workloads.) But, performance has been more than adequate for my purposes, and being able to use certain "exotic" types seemed like a bigger win instead. Binary is definitely more work than dealing with textual formats.

It would be immensely helpful to be able to deal with mixed formats, as it would drastically simplify the code required for postgresql-simple and be a lot more transparent in terms of performance. But the only option there is to either get a patch into libpq and wait, fork libpq, or abandon libpq altogether.

Also, protocol-level parameters are not a complete substitute for interpolation, (e.g. to parameterize table and column names, or to "partially apply" a prepared statement, or the ability to send multiple commands in a single basic query message ) and preparing everything has it's own set of consequences and limitations.

In any case, it's some pretty interesting work, so keep it up. :)

[nikita-volkov]

Implemented since 0.5