Thread by @Xof

Continuing our discussion of #PostgreSQL time types, what about the much-maligned TIMESTAMP type, without TIME ZONE? 1/n

TIMESTAMP and TIMESTAMP WITH TIME ZONE have exactly the same binary representation. TIMESTAMP WITH TIME ZONE means "this timestamp is known to be in UTC." 2/n

TIMESTAMP-no-TZ means "no idea what time zone this particular date and time are in, hope you do." 3/n

The general advice, which is good advice, is to always use TIMESTAMP WITH TIME ZONE, because then the value is fully specified: It is always a precise moment in time, and you need no further information to interpret it. 4/n

So, when might we use plain ol' TIMESTAMP? There are some possibiltiies: 5/n

-- You are receiving date/time values but you don't know at the time of ingestion what time zone they are, and don't want to take the time to convert them right then. 6/n

-- Times that are "always local time." For example, if every one of your stores will close at 4pm on 12/24, and reopen at 10am on 12/26, but they're in different time zones, that might be one way of storing them. 7/n

-- Similarly, calendar systems that want to store an event as "always happens on local time" might use TIMESTAMP-no-TZ as a data type for that. 8/n

Note, though, that these are all edge cases, and require that you have some external source of what time zone the timestamp is in for them to be useful. Because of that, you almost always want TIMESTAMP WITH TIME ZONE. 9/end