3

I have a table which represents time ranges.

   Column    |            Type             |                                Modifiers                                 
-------------+-----------------------------+--------------------------------------------------------------------------
 id          | bigint                      | not null default nextval('exploded_recurring_sessions_id_seq'::regclass)
 schedule_id | bigint                      | 
 start_time  | time without time zone      | 
 end_time    | time without time zone      | 

I'll then do a query such as this:

select from my_table where localtime >= start_time and localtime < end_time;

The business logic here makes sense in the case where end_time is midnight, because I consider a range to be inclusive of start_time and not inclusive of end_time.

Of course, this query doesn't actually work, because localtime will never been < end_time when end_time is 00:00:00.

There are two solutions to this I can think of.

  1. instead of 00:00:00, use '00:00:00'::time without time zone - interval '1 microsecond' for midnight. The likelihood of this every causing an incorrect calculation is probably low enough to never happen. And even if it does happen a handful of times per year, the consequences are essentially nonexistent, at least as my system now stands. However, it's an ugly solution, and using wrong data like this might have other consequences down the line.
  2. select from my_table where localtime >= start_time and (localtime < end_time or end_time == '00:00:00'). This seems pretty straightforward, but it makes it necessary to make one or more special indexes. Not a problem if it's the only solution, but feels like a drawback worth avoiding if possible.

Is there any other way to go about this? Some sort of special datetime function which accommodates this calculation? A better way to represent the data?

1

Community wiki answer:

Is there any other way to go about this? Some sort of special datetime function which accommodates this calculation? A better way to represent the data?

What's wrong with '24:00:00'::time?

24:00:00 is converted to 00:00:00 the next day (in PostgreSQL), so 2018-03-07 24:00:00 is the same as 2018-03-08 00:00:00.

5

Introduction

You might want to consider that 23:59:59.999 belongs to the current day. 00:00:00.000 is considered to be the transition from one day to the next. Let me explain...

Reference: Midnight (Wikepedia).


24 Hour Notation

Depending on whether you are using the 24 hour notation or the 12 hour am/pm notation the following bits of information might be equally interesting.

Midday / Lunch

Midday is not an issue with the 24 hour notation because the step from 11:59:59.999to 12:00:00.000 and onward to 12:00:00.001 have no impact on the time format.

Midnight

Looking at the times around midnight you will find that 23:59:59.999 is still the same day, whereas 00:00:00.001 belongs to the next day. Following this logic the static value 00:00:00.000 should not belong to either day. It is the point of transition.

However, some RDBMS systems will switch the date when adding 0.001 s to 2018-03-07 23:59:59.999.


12 hour notation

Midday / Lunch

Midday in 12 hour notation is neither am nor pm, even though clocks will tend to add a PM as soon as the clock jumps from 11:59:59.999 to 12:00:00.000. Midday should actually have a single m added when static, seeing that a.m. is defined as ante-meridiem which translates to before midday, and p.m. which is post-merdiem translates to after midday.

Midnight

This is equally valid for the 12 hour notation around midnight. 11:59:59.999 p.m is near midnight. 12:00:00.001 a.m. is just after midnight. 12:00:00 a.m./p.m. is midnight and is exactly 12 hours after midday or before the new midday.

However, most systems decide to add an a.m. as soon as the clock reaches midnight at 12 o'clock.


Examples

PostgreSQL

Adding a millisecond to a timestamp value.

Input:

postgres=# select TIMESTAMP '2018-03-07 23:59:59.999' + interval '1 ms';

Output:

 2018-03-08 00:00:00

SQL Server

Adding a millisecond to a datetime value.

Input:

select dateadd(ms,001,cast('2018-03-07 23:59:59.999' as datetime))

Output:

2018-03-08 00:00:00.000

Recommendations

As you can see with the examples, both tested RDBMS systems determine 00:00:00.000 to be the time (after or roundabout) midnight but for the next day.

  1. If you aren't using dates together with the time, then avoid using 00:00:00.000 as the notation for midnight following 23:59:59.999 (or the 11:59:59.999 p.m.)the same day. This corresponds with your solution number 1.

  2. If you are using dates together with the times, then find out which notation your RDBMS supports when adding a microsecond to either 2018-03-07 11:59:59.999 or 2018-03-07 11:59:59.999 p.m.


Full Reference List

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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