How should I represent a midnight end time?

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

• `select from my_table where localtime >= start_time and (localtime

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?

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.

-
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.

-
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

• How should “midnight on…” be interpreted? (English Language & Usage)

• When is it AM and when is it PM? (Arstechnica)

• 12 am - 00.00 (WordReference.com)

• Is midnight 12 a.m. or 12 p.m.? (FAQ - Time) (National Physical Laboratory [U.K.])

• A.M. and P.M. (Greenwichmeantime.com)

• Midnight (Wikepedia)

• 9.9. Date/Time Functions and Operators (PostgreSQL)

• 8.5. Date/Time Types (PostgreSQL)

• DATEADD (Microsoft Docs)

StackExchange Database Administrators Q#199564, answer score: 6