Product: TIBCO Spotfire®
JDBC drivers issue with timestamp/datetime column for data logged during daylight saving day.
This article briefly describes the JDBC drivers' issue when the data is logged during the daylight saving day. Whenever we import the data into Spotfire via JDBC connections, the timestamp of the data logged during the daylight saving hour is extended by an hour. The timestamp of the rest of the data remains the same.
For example, if the starting time of the daylight saving is 2:00 AM and we have the data that is logged like (1 AM, 1:30 AM, 2 AM, 2:30 AM, 3 AM, 3:30 AM, 4 AM, 4:30 AM). When we import that data into Spotfire via JDBC connection, we get entries like ( 1 AM, 1:30 AM, 3 AM, 3:30 AM, 3 AM, 3:30 AM, 4 AM, 4:30 AM ). The timestamp for the dataset logged between (2 AM - 3 AM) will get extended by an hour. So if any entry is logged in the database at 2:20:20 AM, it will be converted to 3:20:20 AM.
Let us suppose we have the data logged on 25th March 2018 in Denmark where the daylight saving starts at 2:00 AM same day.
This appears to be an issue with the JDBC drivers and this doesn't seem to be something that we can change in Spotfire. Instead of JDBC connection, if we use a data connection to that data source we don't see this weird behavior. Apart from that, the results from the DbVisualizer also contributes to this statement.
Refer the following image to see the data imported via a data connection.