Postgres Time with Time Zone Equality

Postgres time with time zone equality

Here are two ways to evaluate timetz equality:

SELECT a, b, a = b AS plain_equality
     , '2000-1-1'::date + a = '2000-1-1'::date + b AS ts_equality
     , a AT TIME ZONE 'UTC', b AT TIME ZONE 'UTC'  AS timetz_equality
FROM  (
   SELECT '12:00:00 -0800'::timetz AS a
        , '14:00:00 -0600'::timetz AS b
   ) sub;

The first by adding it to a date.

The second by using the AT TIME ZONE construct.

But rather don't use time with time zone at all.

Postgres supports the type only because it is in the SQL standard. It is broken by design (cannot consider DST!) and its use is discouraged.

Quoting the manual here:

The type time with time zone is defined by the SQL standard, but the
definition exhibits properties which lead to questionable usefulness.
In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of
date/time functionality required by any application.

how does Postgres handle timezone comparison?

"at time zone" does opposite things when applied to timestamptz versus a timestamp. So applying it twice in a row just gives you back the original. First it does something, then it undoes it.

When applied to timestamptz, it converts the time to look like what it would be expressed as in the indicated time zone, and datatypes it as a timestamp without timezone (except in sqlfiddle, where it seems to do something slightly different, but without changing the overall effect). When applied to a timestamp without timezone, it assumes that that time expressed was already in the indicated time zone, and converts it back to the system time with it datatyped as timestamptz.

how are timestamps with timezones handled in comparison? It seems the zone is ignored and just the values are compared?

You aren't comparing timestamps with timezones. You are comparing timestamps without timezones.

select pg_typeof(now() at time zone 'AEDT');
          pg_typeof          
-----------------------------
 timestamp without time zone

So yes, it ignores the timezones in your comparison, because they are no longer there anymore.

Properly handle TIME WITH TIME ZONE in PostgreSQL

You asserted that:

every TIME column represents a moment during the day specified in REPORT_DATE.

So you never cross the a dateline within the same row. I suggest to save 1x date 3x time and the time zone (as text or FK column):

CREATE TABLE legacy_table (
   event_id      bigint PRIMARY KEY NOT NULL
 , report_date   date NOT NULL
 , start_hour    time
 , end_hour      time
 , expected_hour time
 , tz            text  -- time zone
);

Like you already found, timetz (time with time zone) should generally be avoided. It cannot deal with DST rules properly (daylight saving time).

So basically what you already had. Just drop the date component from start_hour, that's dead freight. Cast timestamp to time to cut off the date. Like: (timestamp '2018-03-25 1:00:00')::time

tz can be any string accepted by the AT TIME ZONE construct, but to deal with different time zones reliably, it's best to use time zone names exclusively. Any name you find in the system catalog pg_timezone_names.

To optimize storage, you could collect allowed time zone names in a small lookup table and replace tz text with tz_id int REFERENCES my_tz_table.

Two example rows with and without DST:

INSERT INTO legacy_table VALUES
   (1, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Vienna')  -- sadly, with DST
 , (2, '2018-03-25', '1:00', '3:00', '2:00', 'Europe/Moscow'); -- Russians got rid of DST

For representation purposes or calculations you can do things like:

SELECT (report_date + start_hour)    AT TIME ZONE tz AT TIME ZONE 'UTC' AS start_utc
     , (report_date + end_hour)      AT TIME ZONE tz AT TIME ZONE 'UTC' AS end_utc
     , (report_date + expected_hour) AT TIME ZONE tz AT TIME ZONE 'UTC' AS expected_utc
     -- START_HOUR - END_HOUR
     , (report_date + start_hour) AT TIME ZONE tz
     - (report_date + end_hour)   AT TIME ZONE tz AS start_minus_end
FROM   legacy_table;

You might create one or more views to readily display strings as needed. The table is for storing the information you need.

Note the parentheses! Else the operator + would bind before AT TIME ZONE due to operator precedence.

And behold the results:

db<>fiddle here

Since the time is manipulated in Vienna (like any place where silly DST rules apply), you get "surprising" results.

Accounting for DST in Postgres, when selecting scheduled items
Ignoring time zones altogether in Rails and PostgreSQL

Comparing TIME WITH TIME ZONE returns unexpected result

now()::time at time zone 'Europe/London'

... returns a value of time with time zone (timetz):

Then you compare it to time [without time zone]. Don't do this. The time value is coerced to timetz in the process and a time offset is appended according to the current timezone setting. Meaning, your expression will evaluate differently with different settings. What's more, DST rules are not applied properly. You want none of this! See:

db<>fiddle here

More generally, don't use ~~time with time zone (timetz)~~ at all. The type is broken by design and officially discouraged in Postgres. See:

Postgres time with time zone equality

Use instead:

SELECT (now() AT TIME ZONE 'Europe/London')::time > '22:00:00'
   AND (now() AT TIME ZONE 'Europe/London')::time < '23:35:00' AS is_currently_open;

The right operand can be an untyped literal now, it will be coerced to time as it should.

BETWEEN is often the wrong tool for times and timestamps. See:

How to add a day/night indicator to a timestamp column?

But it would seem that >= and <= are more appropriate for opening hours? Then BETWEEN fits the use case and makes it a bit simpler:

SELECT (now() AT TIME ZONE 'Europe/London')::time
       BETWEEN '22:00:00' AND '23:35:00' AS is_currently_open;

Perform this hours of operation query in PostgreSQL

Postgres time comparaison with time zone

(CURRENT_TIME AT TIME ZONE 'Europe/Paris') is, for example, 17:52:17.872082+02. But internally it is 15:52:17.872082+00. Both time and timetz (time with time zone) are all stored as UTC, the only difference is timetz is stored with a time zone. Changing the time zone does not change what point in time it represents.

So when you compare it with a time...

# select '17:00:00'::time < '17:52:17+02'::timetz;
 ?column? 
----------
 f

That is really...

# select '17:00:00'::time < '15:52:17'::time;
 ?column? 
----------
 f

Casting a timetz to a time will lop off the time zone.

test=# select (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
    timezone     
-----------------
 17:55:57.099863
(1 row)

test=# select '17:00:00' < (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
 ?column? 
----------
 t

Note that this sort of comparison only makes sense if you want to store the notion that a thing happens at 17:00 according to the clock on the wall. For example, if you had a mobile phone game where an event starts "at 17:00" meaning 17:00 where the user is. This is referred to as a "floating time zone".

Assuming day is "day of week", I suggest storing it as an integer. It's easier to compare and localize.
Instead of separate start and end times, consider a single timerange. Then you can use range operators.

In PostgreSQL, can we directly compare two timestamp with different time zone?

Postgresql has two different timestamp data types and it is confusing which one should be used when. The two types are:

timestamp (also known as timestamp without time zone) It is most likely that this is the type in table_a
timestamp with time zone This is the data type returned by to_timestamp()

You must be sure that you are comparing apples with apples or pairs with pairs and not mix them or you may get undesirable results.

If your table_a.time_1 is a timestamp with time zone then the code you give in your question will work fine.

If your table_a.time_1 is a timestamp then you will need to change your code:

SELECT * 
  FROM table_a 
 WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ') at time zone 'utc';

The last part of this (at time zone 'utc') will strip the timezone (PDT) off the specified timestamp and translate the timestamp to UTC.

Edit: to help with your comments in this answer...

In order to understand how to translate time zones you need to understand the difference between the two forms of time stamp. It will become clear why you need to understand this below. As I indicate above the difference between the two forms of time stamp is confusing. There is a good manual page but for now just read on.

The main thing to understand is that neither version actually stores a time zone (despite the name). The naming would make much more sense if you added an extra word "translation". Think "timestamp without time zone translation" and "timestamp with time zone translation".

A timestamp with time zone translation doesn't store a time zone at all. It is designed to store time stamps which could come from anywhere in the world and not loose track of their meaning. So when entering one you must provide the time zone it came from or postgresql will assume it came from the time zone of your current session. Postgresql automatically translates it out of the given time zone into an internal time zone for the server. You don't need to know what time zone that is because postgresql will always translate it back from this internal time zone before giving you the value. When you retrieve the value (eg: SELECT my_time FROM foo) postgresql translates the time stamp to the time zone of your current session. Alternatively you can specify the time zone to translate into (eg: SELECT my_time AT TIME ZONE 'PDT' FROM foo).

With that in mind it's easier to understand that a timestamp without time zone translation will never be changed from the time you specify. Postgresql will regard 11:00:00 as happening before 12:00:00 even if you meant 11 in America and 12 in England. It's easy to see why that may not be what you want.

A very common programming error is to think that a timestamp with time zone is at a particular time zone. It isn't. It is at whatever time zone you ask for it to be. And if you don't specify what time zone you want it at then postgresql will assume you want it at your current session time zone.

You've stated that your field is a timestamp with time zone which are all at UTC. This isn't technically correct. Most likely your session time zone is UTC and postgresql is giving you everything in UTC as a result.

So you have a timestamp with time zone and you want to know what these times are in PDT? Easy: SELECT my_time AT TIME ZONE 'PDT' FROM foo.

It's important to understand that the AT TIME ZONE '...' syntax toggles between timestamp and timestamp with time zone.

timestamp AT TIME ZONE 'PDT' converts into a timestamp with time zone and tells postgresql to convert to the PDT time zone.
timestamp with time zone AT TIME ZONE 'PDT' converts into a timestamp telling postgresql to interpret it as coming from 'PDT'.

This symetry means that to reverse AT TIME ZONE 'foo' you just use AT TIME ZONE 'foo'. Put another way SELECT anything AT TIME ZONE 'PDT' AT TIME ZONE 'PDT' will always leave anything unchanged.

Saving time.Time in golang to postgres timestamp with time zone field

These times are not equal:

// {2020-02-25 12:37:16.906605805 +0000 UTC ...}
// {2020-02-25 12:37:16.906606 +0000 UTC ...}

the DB value has (rounded) micro-second precision - go's time has nano-second precision.

I would suggest rounding your times before you add them to the database to a level of precision that is supported by your DB and your needs e.g.

a.Atime = a.aTime.Round(time.Microsecond) // round to nearest micro (per Markus comment)

res, err := db.Exec("INSERT INTO my_table VALUES ($1, $2, ...) RETURNING id", a.aTime, ...)

Also to compare time equality, use time.Equal():

Equal reports whether t and u represent the same time instant. Two
times can be equal even if they are in different locations. For
example, 6:00 +0200 and 4:00 UTC are Equal. See the documentation on
the Time type for the pitfalls of using == with Time values; most code
should use Equal instead.

Compare a date and a timezone to a timestamptz in Postgresql

SELECT run_date::timestamp AT TIME ZONE customer_timezone < '2017-10-15T06:00:00Z'::timestamptz
FROM scheduled_tasks;

In this query I first create the time in particular timezone, then the expected run time at the UTC, and compare.

Postgres Time with Time Zone Equality