PostgreSQL – How to Constrain Timestamp to End of Day

constraintdatatypespostgresqltimestamptimezone

Given a table named bananas
and a timestamp without time zone column named end_time
and some psql clients have set timezone to 'UTC'
and other psql clients have set timezone to 'US/Eastern'
and the server config has timezone = 'UTC' in postgresql.conf

How would one write a check constraint on bananas.end_time to ensure that end_time is always the end of the day, defined as the 23rd hour and 59th minute and 59th second of a "US/Eastern" day?

I tried:

alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  23 = date_part('hour', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = date_part('minute', end_time at time zone 'UTC' at time zone 'US/Eastern') 
  and 59 = floor(date_part('second', end_time at time zone 'UTC' at time zone 'US/Eastern')) 
)
;

This seems to correctly constrain the column, but it seems horrendously inefficient, and quite unreadable. Is there a more efficient and/or more readable implementation?

Best Answer

For now

While stuck with your unfortunate solution:

CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time)

That's right, AT TIME ZONE two times:

  • The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times.

  • The second instance converts the timestamptz back to timestamp at your given time zone. Now you can just check that the time component is whatever you wish.

Cast to time, instead of converting to a string, that's cheaper and more robust.

To get rid of fractional digits you could cast to time(0) instead, but that rounds the values instead of floor in your example. Instead, truncate with date_trunc(), which is the cheaper method for floor() with positive numbers:

CHECK ((date_trunc('sec', end_time) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time
        = '23:59:59'::time)

Proper solution

timestamp values have fractional digits and using the time component '23:59:59' as upper limit is an unfortunate decision. Instead, use 00:00 of the next day as exclusive upper border. It is trivial to enforce that with a check constraint.

Next, since you are dealing with multiple time zones, I would suggest to use timestamptz instead of timestamp. Internal storage is the same as with timestamp in the given time zone 'UTC', but input / output handling is different.

  • The timestamp is shifted to the current time zone automatically on output.
  • Input timestamps with time zon offset so the values are saved as according UTC times automatically.

Related answer on SO with a lot more details.

Related Question