4

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?

4
  • looks readable to me. You can improve the readability by moving and operators to the end of previous lines :)
    – cha
    Commented Aug 26, 2014 at 1:47
  • What happens when the day passes and tomorrow comes? Should the constraint still hold (and all values of the column, in all the table rows, changed)? Commented Aug 26, 2014 at 8:40
  • I suggest you just add a default value for the end_time column. But it seems really confusing to set the server config in UTC but the values in the column in US/Eastern. Why? Commented Aug 26, 2014 at 8:41
  • Define "end of the day" more closely. End of which day? Also, I would approach the task differently to begin with .. Commented Aug 26, 2014 at 16:22

3 Answers 3

6

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.

3
  • 1
    And if they decide to store 00:00, then there is no point in using timestamps at all, is it? A simple date, either today's or the tomorrow value would suffice. Commented Aug 26, 2014 at 16:54
  • @ypercube: Exactly. Attention to one detail, though: When casting a date to timestamp / timestamptz the current time zone setting is assumed. Needs to be offset to 00:00 UTC if that is the intended meaning of the "date". That makes timestamptz a bit simpler for the purpose than a date. But a date just needs 4 bytes (timestamp: 8 bytes). Commented Aug 26, 2014 at 17:20
  • Wow. Awesome answer, thanks! I believe that the type of the column is dictated by my ORM, but I can probably convince my team to use the start of day i+1 instead of the end of day i. Good idea!
    – Jared Beck
    Commented Aug 26, 2014 at 19:50
3

You may use to_char to get the time fields from a single function call:

check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59')

Seconds given by SS are not rounded up so that should be OK as an equivalent to floor

1
  • I'm definitely going to try this. It should be more efficient because the zone conversion only happens once. Thanks!
    – Jared Beck
    Commented Aug 26, 2014 at 14:49
0

Try this. It maybe be faster, but still unreadable:

alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = 
   date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern')
)
;
1
  • I had trouble before with the interval and date_trunc approach. I'll have to look at yours closely and see what you've done differently. Thanks!
    – Jared Beck
    Commented Aug 26, 2014 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.