DateTime.FromString Does Not Handle Timezone Shifts/Jumps

General
1

DateTime.FromString does not correctly handle creation of DateTime objects if the string passed happens to fall in a timeframe that was “skipped” in history due to a timezone shift. For example, on Jan 1, 1933 Singapore jumped ahead, so if you try to construct a DateTime with “1933-01-01 00:00:00” Xojo throws the following exception:

Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD

This directly contradicts the Xojo documentation for DateTime, which states:

When no locale is specified, the dateValue parameter can be in either SQLDate (YYYY-MM-DD) or SQLDateTime (YYYY-MM-DD HH:MM:SS) formats.

(Note that the docs say to include the Seconds, while the exception indicates they should be dropped)

If you instead try “1933-01-01 00:00” (drop the seconds, per the exception message) it fails with the exact same exception message.

I’d suggest that throwing an exception in this case is the wrong way to handle it. Here is a screencast I created that goes over what I found, how I found it, and what I suggest should be done instead:

There are hundreds (if not thousands) of edge cases throughout history when timezones shift and skip time, leaving holes in the timeline of certain timezones. These need to be handled gracefully somehow.

Should I open a feedback issue with this? I see there have been several in the past… but the problem persists.

1 Like
2

You need to define ‘somehow’ and open a feature request.

For example, if my string in the US (most places) is “2025-03-09 02:25:00” what do I want Xojo to give me instead of a RuntimeException?

  • a datetime like 2025-03-09 03:00:00
  • a datetime like 2025-03-09 03:25:00
  • other option

You can make a bug report about the exception on invalid date time (it should say invalid date instead of need input as SQLDate or SQLDateTime).

You can make another bug report for the missing seconds on the SQLDateTime.

Does your application needs/uses local time? If not, maybe you can use UTC dates to avoid time zone problems.

1 Like
3

This issue https://tracker.xojo.com/xojoinc/xojo/-/issues/72471 has many related issues that all mention a similar problem.

4

Well, given that the DateTime is applying a timezone (taken from the local OS current locale) when one is not specified, DateTime (or whatever underlying library Xojo uses) should already have the correct logic to determine if a given time falls into one of these edge cases, and move it to whatever the correct representation should be for that given locale/timezone.

In the specific instance I provided of 1933-01-01 in Singapore, the correct behavior is to simply move the time forward 20 minutes (which is what actually happened in Singapore in 1936).

However, that is not necessarily what should be done in all cases. For instance, when clocks move backwards for daylight savings time and you specify a time that lies in the hour that is repeated, this becomes an ambiguous time - in that case, I’d recommend picking the earlier of the 2, and set the corresponding time zone to indicate it chose the instance before the clocks changed.

What baffles me is that all the historical time zone stuff has been well documented for many, many years, and I assume (perhaps naively) that the underlying libraries Xojo is leveraging have ways to deal with all this.

I recognize that these are edge cases, and Timezone changes and daylight savings time in general make programmers insane… but simply barfing is just not good enough.

When I say I want something graceful: Don’t just puke up an exception with a completely incorrect error message. Either fix DateTime to correctly handle edge cases, or clearly document that this is a problem with the DateTime class.

For my given situation, since these are birthdates and the actual wall clock time is not relevant, I’ll just wrap the parsing in a try, and it if fails I’ll re-try it with the time moved forward, say, 6 hours. Nearly all my birthdates are stored in the database with 00:00:00 as the time component, so moving them to 6 am when there is a parse error will band-aid this for me… but this really should be fixed correctly by Xojo.

I’ve considered opening a feedback case, but there have already been multiple feedback cases surrounding these kinds of problems with DateTime - some marked as fixed, some still outstanding. I’m not sure another report on top of the pile will make any difference.

1 Like
5

After more research, this appears to be a problem for me specifically because I used to use the now deprecated Date class, which did not have any real respect/enforcement of daylight savings time offsets and transitions. I recently went through my code and updated everything to use DateTime instead of the old Date class, thus why I now have date strings in my database which represent invalid times for certain timezones.

sigh

6

I agree with you that Xojo should handle dates that are invalid if time is 00:00:00.
“1933-01-01” should not fail and the DateTime created should be the first valid DateTime for the day, no matter if it is 01:00:00 or other value for the time.
Looks like other languages do this automatically.

For other dates (like “2025-03-09 02:25:00” for USA, or “2025-01-32”), the RuntimeException should report “Invalid Date” and not "use YYYY-MM-DD.

7

Forgive me but I know we talked about cases like this for a long time internally and it was decided that times that didn’t exist should throw an exception. You are, after all, providing it with invalid data. Now, I also agree that the documentation is incorrect if not specifying the time zone actually sets the timezone to Timezone.Local and not Nil.

FWIW, You might want to try explicitly setting the timezone to Nil when coming from dates created with the Date class… and remember that the two use very different epoch dates. IIRC, date is 1900-01-01 and DateTime is 1970-01-01.

8

Hi Greg - thanks for weighing in.

If it throws an exception, at least make the exception contain an accurate message. The message in my case is not only inaccurate, it contradicts the documentation entirely, and does not provide any real value to the programmer pulling their hair out about why some (nearly all) dates parse properly, while some don’t, and only for some users worldwide.

Further: multiple other development environments have solved this particular issue in clean/elegant ways. A brief bit of Google Fu earlier today shows that I believe libraries exist on both Windows and Mac at least to disambiguate and adjust timestamps to something reasonable and valid. But if Xojo has decided that’s not worth the effort, then please at least fix the message raised by the exception and/or update the documentation to be more verbose.

In the mean time: I’ve solved this by (pseudo code, duh):

while DateTime.fromString() barfs on the database value
increment the hour in the database by 1
wend

For my needs, this solves the problem. It’s a horrible hack, but it does not affect ultimate accuracy of my patient records so it’s fine.

9

This is a perfectly reasonable (and, I believe, a relatively low-effort) approach. It would also solve my particular problem. Unfortunately, it appears Xojo has decided not to do so, per Greg.

10

Per William on case #71555

[RN] DateTime.FromString no longer throws an InvalidArgumentException when the date (without a time specified) falls within the DST transition period.

Looks like William partially fixed the problem, this works (for Montevideo Uruguay):

dteFECNAC = new DateTime(1974, 12, 22)

and gives us 1974-12-22 01:00:00 (because 00:00:00 does not exist)
but

dteFECNAC = DateTime.FromString("1974-12-22")

fails with RuntimeException.

I expect both should work the same.

Edit: more testing, this

dteFECNAC = New DateTime(1974, 12, 22, 0, 10, 0)

gives back a DateTime of 1974-12-22 01:10:00

image
image1244×350 55.5 KB

we just need Xojo to fix FromString and get the same results.

Edit2: after re-reading what William wrote, I think maybe the fix mentioned for FromString is not working anymore? I don’t remember testing this with 2024r1.

11

The industry uses the ICU libs. And the behavior should follow the standards.

12

I agree that the error message should be accurate, but since the error itself is inconsistent with how Xojo normally does things, I’d say that this error is probably coming directly from the underlying framework, without modification.

I suggest filing your bug report on that basis, that the exception message doesn’t make sense.

Something else to consider… the fact that the date & time you have could was accepted by the Date class could be a bug in the Date class. That is, that the Date class had a bug in its lookup tables for when daylight savings occurred in that year and that this new framework is more accurate.

13

Created a couple of issues:

78191 may be closed as there are 2 ‘Feature Requests’ that are similar (from 2 and 3 years ago).

@Kimball_Larsen in the mean time maybe you can create your own FromSQLDate that checks if the format is YYYY-MM-DD HH:MM:SS and then pass each element to New DateTime(YYYY, MM, DD, HH, MM, SS) that way you can avoid your original problem and you will get a date for 1/1/1933 with time 01:00:00 instead of an exception.

1 Like
14

Thank you very much for opening those feedback issues, Alberto!

15

Normally I’d tend to agree, but when I hit up Google to search for that exact error message (and a few permutations of it), the only instances I can find of it are from Xojo. If it were a message from a deeper framework, there would undoubtedly be more references to it from outside of Xojo.

You bring up a good point about potential bugs in the old Date class - but I suspect it’s worse than you suggest: roughly 2 weeks ago I released a new version of an app that has thousands of users with millions of records worldwide across the user base. Prior to this release (using the old Date class), I never had any problems with any of this. Since that release (using DateTime), I’ve heard from multiple customers (and at an increasing rate - 1 right before Christmas, several more last week, even more as recently as this morning) that this issue is coming up for them. I’ve only shared 2 example dates (1933-01-01 in Singapore, 1942-09-01 in India) but there are a bunch more. This leads me to believe that rather than there being an isolated whoopsee in the lookup tables for Date, there was a much larger bug where the old Date class simply did not care for DST at all and would happily create invalid dates.

At any rate, I’ve put a band-aid on the issue by catching the exception thrown by fromDate and incrementing the hour until it no longer throws an exception. In all my test cases this provides a valid DateTime object on the correct day which is perfect for my use-case. This would not be ok if I cared about the time - but these are birthdates, so the time is actually irrelevant.

16

You might want to change your fix to fall back by an hour at the right time of year instead of always going forward for true accuracy…

But I’d also like to point out that this is a great reason to have unit tests in your code.

17

In my specific case, I’m storing a birthdate. They are stored with a time of midnight on the day of the birthday.

So if I have 1933-01-01 00:00:00 as the birthdate, I cannot roll backwards by an hour, because then the date is 1932-12-31 23:00:00. That’s the wrong day. I suppose I could set the time to 1933-01-01 23:00:00, and decrement the hour that way, but that over-complicates things imho.

By simply moving the date forward an hour, it works fine.

In fact, I’ve been cheating and using the old Date class to construct a new Date, set it’s sqlDateTime = the value from my db, and just ++ the hour.

However, now that I’ve discovered (in the feedback report) that the constructor for DateTime correctly handles these time shifts, and that the DateTime constructor will happily take an old Date instance, I’m going to alter my code not to shift the hour at all - but rather to construct an old Date instance and feed that to the constructor of DateTime.

18

I was under the impression that the fix is not part of current Xojo but I’m wrong.
The fix is:

[RN] DateTime.FromString no longer throws an InvalidArgumentException when the date (without a time specified) falls within the DST transition period.

now, instead of getting an InvalidArgumentException we get RuntimeException. What this means? that if we try/catch for RuntimeException and do nothing the DateTime has the correct time (01:00:00).

Sample code:

Var d1, d2 As DateTime
Var tz As timezone = New TimeZone("Asia/Singapore")
//d1 = New DateTime(1933,1,1,0,0,0,0,tz)  //work
try
  d2 = DateTime.FromString("1933-01-01",Nil,tz) //RuntimeException but not InvalidArgumentException
Catch e as RuntimeException
  //nothing to fix here
end try
Break //d2 is 1933-01-01 01:00:00

so, if you only care about dates and not use time, that should fix your problem @Kimball_Larsen

Edit:
If you provide a time (like US DST 2025-03-09 02:00:00) we get RuntimeException and then InvalidArgumentException, but as long as the time is not used you should only get RuntimeException (I think this is a bug and should not throw this exception).

19

how are you going to construct that old Date that you can’t use DateTime?

20

On a computer with the time zone set to Asia/Singapore:

var d as Date = new Date
d.sqlDateTime = '1933-01-01 00:00:00'
var dt as DateTime = new DateTime(d)

No exceptions are thrown, and dt is now valid DateTime set correctly to ‘1933-01-01 00:20:00’ (the correct offset for Singapore on that date and time).

1 Like