Issue 1: Invalid day exception

When the output in the current server timezone would cause the month to change forward, strptime() fails with the exception:

strptime('2017-07-31T22:30:00-07:00', '%Y-%m-%dT%H:%M:%S%z')
Evaluation exception: Invalid day: 32, valid range 1 - 31

Issue 2: Daylight Saving Time 

Between hours 23:00:00.000 and 23:59:59.999 on the day of the end of Daylight Saving Time there is an hour offset from the expected time. The time conversion before and after the actual time change (for example, 02:00 November 5 2017 EDT) are correct.

The reported times are (example):

strptime('2017-11-05 22:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-05 22:00:01.000-0500 good
strptime('2017-11-05 23:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-06 00:00:01.000-0500 bad
strptime('2017-11-06 00:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-06 00:00:01.000-0500 good
strptime('2017-11-06 23:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-06 23:00:01.000-0500 good

The strptime() function can fail or give unexpected output.

Issue 1: Invalid day exception

This is bug "SB-30544 strptime Invalid day exception on valid date" which causes the above exception when the month would change to a later value based on the change in reported timezone. If the month or year changes to an earlier value, this exception does not happen. This bug has not been fixed as of this writing (November 2017, StreamBase 7.7.1 and 10.1.1) but may be fixed in a later release.

The workaround is to use 'parse_time(time,format)':

Example for EST timezone:

parse_time("2017-07-31T22:30:00-07:00", "yyyy-MM-dd'T'HH:mm:ssX")
(timestamp) 2017-08-01 01:30:00.000-0400

 

Issue 2: Daylight Saving Time 

This is bug "SB-42607 Incorrect millisecond time from strptime() at 23:00 at end of DST" which causes the internal millisecond timestamp value after conversion to be one hour later than expected between hours 23:00:00.000 and 23:59:59.999 on the day of the end of Daylight Saving Time. This bug has not been fixed as of this writing (November 2017, StreamBase 7.7.1 and 10.1.1) but may be fixed in a later release.

This does not affect other dates, and does not affect the correct processing of dates around the actual DST time change.

The correct time conversion at the hour of the DST changeover looks like this (example):

Start of DST:

strptime('2017-03-12 00:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-03-12 00:00:01.000-0500
strptime('2017-03-12 01:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-03-12 01:00:01.000-0500
strptime('2017-03-12 02:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-03-12 03:00:01.000-0400 change
strptime('2017-03-12 03:00:01-04:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-03-12 03:00:01.000-0400

End of DST:

strptime('2017-11-05 00:00:01-04:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-05 00:00:01.000-0400
strptime('2017-11-05 01:00:01-04:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-05 01:00:01.000-0400
strptime('2017-11-05 02:00:01-04:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-05 01:00:01.000-0500 change
strptime('2017-11-05 01:00:01-05:00', '%Y-%m-%d %H:%M:%S%z') --> (timestamp) 2017-11-05 01:00:01.000-0500

NOTE: If you supply a timezone offset which is not correct for that date in your locale (for example, a -0400 offset for a -0500 locale), the calculated GMT time will be offset forward or backward based on the Gregorian Calendar timezone offset for your locale. Dates and times without a timezone offset will be interpreted as if generated in the local timezone. Not considering the difference between the system's locale timezone and the supplied timezone in the date-time value often results in output which appears to be incorrect by one hour, particularly on the date of a DST change.

The workaround is to use 'parse_time(time,format)':

Example for EST timezone:

bad:  strptime(  "2017-11-05 23:00:01-05:00", "%Y-%m-%d %H:%M:%S%z")  --> (timestamp) 2017-11-06 00:00:01.000-0500
good: parse_time("2017-11-05 23:00:01-05:00", "yyyy-MM-dd HH:mm:ssX") --> (timestamp) 2017-11-05 23:00:01.000-0500