Mid-America Db2 User Group

I got called into a issue yesterday related to a Day Light savings time, that I wanted to share.

The Issue was related to a JAVA application were users were getting a error on a date time. 

During the conversation the error was related to JAVA  and not DB2.  But being good DBA's that are went through process of validating that is was not a database issue.

After spending a lot time checking the database, in this case db2, and a stored procedure that return a result, we found no database issues.  Our thought was maybe the database was somehow returning data in the result set that Java was not handling.

The underlying issue was JAVA code where a timestamp value was be place into a Java timestamp data type were the timestamp value was during the gap in day Light saving time period.

Did you know that Java has a mechanism that won't let it create an invalid timestamp or date, similar to what our databases won't allow to do, like creating a February 30^th date, but Java also supports Day light savings.   I never considered or at least over look this is how would the database handle creating a valid time stamp but with a time in the Day Light Saving Time Gap (Change) for the Spring adjustment.    

The Java error they got was because they were trying to create a date of 3/12/2023 with a time value in the 02:00 range (GAP; Remember that GMT is in play here for the central time zone), which would be an invalid time because time it is the gap period of Day Light Saving time change (when the logical clock jumps for from 11:59 pm to 1:00 am).

So how would the database handle this; would we get an invalid timestamp or something else for the same time frame of '2023-03-12-02.05.00.00000'.  Well I have a very simple test below for DB2 Mainframe and LUW and both took the invalid time and rolled it forward to the next hour, so no -181 for invalid value.  Not sure how Oracle and MS SQL server handle the cap time.

As for the Java application they are going to have fix the code to account for the Day Light Saving time change period….

Here is my simple test. (Remember that GMT is in play here as well) that shows the Gap time period of 2:05 (row 3) and how in Db2 that time jumps to 3:05

select 1, timestamp('2023-03-12-00.05.00.00000')  from sysibm.sysdummy1 union all 

select 2, timestamp('2023-03-12-01.05.00.00000')  from sysibm.sysdummy1 union all

select 3, timestamp('2023-03-12-02.05.00.00000')  from sysibm.sysdummy1 union all

select 4, timestamp('2023-03-12-03.05.00.00000')  from sysibm.sysdummy1 union all

select 5, timestamp('2023-03-12-04.05.00.00000')  from sysibm.sysdummy1

order by 1 ;

Result returned.

- ---------------------

1 2023-03-12 00:05:00.0

2 2023-03-12 01:05:00.0

3 2023-03-12 03:05:00.0

4 2023-03-12 03:05:00.0

5 2023-03-12 04:05:00.0