Is there a bug in timestamp - I strongly believe, it is


#1

If timestamp comes in following format, Sat Sep 17 2016 20:00:00 GMT-0500 (Central Daylight Time), it is impossible to get 20 or 8 PM as the hour. The database adds GMT (500 ) and then gives 1 in the morning. There are only 12 hours available and when 13th hour is reached, it adds one to the date.

1 <- Hour

25 <- Day extracted from the date

Thu Jul 24 2014 20:00:00 GMT-0500 (Central Daylight Time) <- trunc hour from the date

Thu Jul 24 2014 20:30:00 GMT-0500 (Central Daylight Time) <- Real date from the data

I lost a whole deal of work after discovering this blatant hole in the software. Very immature.


#2

Hi @j1axs01

the system stores the timestamps converting them into UTC; it doesn’t store the TZ portion of the data.

mapdql> insert into test_ts values (1,'2016-09-17 20:00:00 -0500');
Execution time: 109 ms, Total time: 110 ms
mapdql> insert into test_ts values (2,'2016-09-17 20:00:00 -0000');
Execution time: 10 ms, Total time: 11 ms
mapdql> insert into test_ts values (3,'2016-09-17 20:00:00 +0500');
Execution time: 10 ms, Total time: 11 ms
mapdql> select * from  test_ts;
1|2016-09-18 01:00:00
2|2016-09-17 20:00:00
3|2016-09-17 15:00:00

adding 5 hours to 8 pm it’s normal to get 1 am of the next day.