PostgreSQL – How to Represent a Midnight End Time

datetimepostgresqltime

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.

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

Best Answer

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

THIS IS THE QUERY YOU NEED

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SET @nexttime = 0;
SELECT
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) 
;

SAMPLE DATA

mysql> use test
Database changed
mysql> drop table if exists airport_taxi;
Query OK, 0 rows affected (0.13 sec)

mysql> create table airport_taxi
    -> (id int not null auto_increment primary key,
    -> date date,time time,direction varchar(10));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into airport_taxi values
    -> ( 7,'2014-04-15','21:35:00','to'),
    -> ( 8,'2014-04-15','15:20:00','from'),
    -> ( 9,'2014-04-15','17:35:00','to'),
    -> (10,'2014-04-15','08:15:00','to'),
    -> (11,'2014-04-15','14:40:00','to'),
    -> (12,'2014-04-15','11:35:00','to'),
    -> (13,'2014-04-15','14:35:00','from');
Query OK, 7 rows affected (0.21 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from airport_taxi;
+----+------------+----------+-----------+
| id | date       | time     | direction |
+----+------------+----------+-----------+
|  7 | 2014-04-15 | 21:35:00 | to        |
|  8 | 2014-04-15 | 15:20:00 | from      |
|  9 | 2014-04-15 | 17:35:00 | to        |
| 10 | 2014-04-15 | 08:15:00 | to        |
| 11 | 2014-04-15 | 14:40:00 | to        |
| 12 | 2014-04-15 | 11:35:00 | to        |
| 13 | 2014-04-15 | 14:35:00 | from      |
+----+------------+----------+-----------+
7 rows in set (0.00 sec)

QUERY EXECUTED

mysql> SET @GivenDate = '2014-04-15';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
    -> FROM airport_taxi WHERE date=@GivenDate;
Query OK, 1 row affected (0.00 sec)

mysql> SET @nexttime = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     TIME(FROM_UNIXTIME(StartTime)) "start",
    ->     TIME(FROM_UNIXTIME(EndTime)) "end",
    ->     FLOOR((EndTime - StartTime)/60) diff_min
    -> FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
    -> FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
    -> FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
    -> UNION SELECT
    ->     TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    ->     FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    ->     UNIX_TIMESTAMP(@LastDateTime))/60)
    -> ;
+----------+----------+----------+
| start    | end      | diff_min |
+----------+----------+----------+
| 08:15:00 | 11:35:00 |      200 |
| 11:35:00 | 14:35:00 |      180 |
| 14:35:00 | 14:40:00 |        5 |
| 14:40:00 | 15:20:00 |       40 |
| 15:20:00 | 17:35:00 |      135 |
| 17:35:00 | 21:35:00 |      240 |
| 21:35:00 | 00:00:00 |      145 |
+----------+----------+----------+
7 rows in set (0.00 sec)

mysql>

Just remember to set the @GivenDate to whatever date you need

BTW Your last line is based on midnight the next day. Therefore, the diff_min is 145 not 25. If you have a knock-off time of 10:00 PM at th eairport, then change

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)

to this

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) - 120

Subtracting 120 backs 2 hours away from midnight of the next day makes the last line's end 10:00 PM

Give it a Try !!!

UPDATE 2014-04-16 09:44 EDT

I cannot really test this, but you can try this:

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SELECT id INTO @LastID FROM airport_taxi
WHERE date=@GivenDate ORDER BY time DESC LIMIT 1;
SET @nexttime = 0;
SELECT
    id,
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT id,(@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT id,FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT @LastID,
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) 
;

I hope this helps !!!

Best Answer

Related Solutions

Postgresql – How should I represent a latitude and longitude in Postgres without using PostGIS

MySQL – How to Select Different Time Formats

THIS IS THE QUERY YOU NEED

SAMPLE DATA

QUERY EXECUTED

Give it a Try !!!

UPDATE 2014-04-16 09:44 EDT

Related Question