Hi @TotalDataLoss, you certainly aren’t alone in finding problems with the date time formatting. The date formats come from Java’s (poorly named imho “SimpleDateFormat” class, which contains illogical inconsistencies and poor design decisions (as I said, IMHO )
The format letters are of course in the help documentation for the node, but it doesn’t help with the subtlety of “how” it works.
For example as you have already pointed out capital M is Month but lower case m is minute. Ok, I can live with that bit as they needed the letter “m” for both, and had to make a distinction, but then we have that MM is a two-and-only-two digit month, whereas M means “either one or two digits”, but MMM means Month Name, except that the string it interepts is case-sensitive so in the UK it cannot understand “jan” or “JAN”, but only “Jan” (a ridiculous design decision for what should be a “simple” date format, and worse than that, they had to make it case-sensitive based on the locale, so we cannot easily standardise on just throwing something at it that contains the correct letters in the correct order. In the UK it’s Title case, but in Italy its lower case - because in Italy months are not written with any capitals, whereas in the UK generally they are)
The we look at the
m situation. So
mm is a forced-two-digit minute, whereas
m allows one or two digit minutes…
ss presumably do the same for seconds, and then we get to Milliseconds - described in the node help as “fraction of a second” - (where the designers ran out of variations on the letter
M, so used capital
S) and then people naturally assume that when it comes to milliseconds, that maybe
.S should cover it. “
s” covers one or two digit seconds, so why shouldn’t
S cover all the variations of one, two or three digit Milliseconds?.. but no!
S means exactly one digit (tenths),
SS is exactly two digits (hundredths), and
SSS is exactly three digits (thousandths/millseconds), so to cover the bases for all variations (down to) Milliseconds, we have to put in
[[.SSS][.SS][.S]] and that isn’t going to be obvious to anybody until they hit that particular wall!
Then we have Year, where we can choose, two or four digit years with
yyyy, and what happens when we have dates such as:
representing 3 Feb 2001 ?
We try our date format
yy-M-d and it chokes with 2001.
We try our date format
yyyy-M-d and it chokes 01 .
Then we scratch our heads and maybe think how clever the designers were in allowing us just to put in
y-M-d… And that’s what we try, and it doesn’t shout at us that it’s wrong, so we get on with our lives, not realising that “
y” handles both 2 and 4 digit years but not in the way that we think it will. It doesn’t use the generally recognised standard for dealing with two-digit dates, (i.e. based on a cut-off year go with either 19xx or 20xx)…oh no… instead it interprets 01-02-03 as 0001-02-03
So for year, if we want to be sure to cover both 4 and 2 digit, we end up with something like
[yyyy][yy]-M-d which leaves some room for errors in the data!
Well that’s got that off my chest.
No…I really can’t understand why anybody has problems with date and time formats…