How to convert DateList format to standard SQL format?

I have a date in standard SQL format YYYY-MM-DD HH:MM:SS.xx, where the xx are hundredths of seconds:

x = 2016-05-30 11:58:46.84


I have a file with 200,000+ records like this. I need to compare this file to a similar file with (mostly) the same information where, however, the seconds are expressed with 6 decimals. So I need to pad zeros on the right of the seconds in the above. Here is what what I did. (NB: I am using the indentation formatting liberally to help readability, even when I am not actually entering MMA code.)

I transformed x to DateList format:

y = DateList[x] = {2016, 5, 30, 11, 58, 46.84}


Then I picked the 6th entry of the list and padded the zeros on:

z = NumberForm[y[[6]], {5, 6}] = 46.840000


Now I have to rebuild the original SQL datetime number carrying along the newly padded zeros:

y[[6]] = z;


Such that now

y = {2016, 5, 30, 11, 58, 46.840000}


I am stuck on the very last step. How do I go from the latest format of y above back to

 2016-05-30 11:58:46.840000 ??


I looked for type or format conversion functions but no joy so far. Apologies if this is a stupid question with an obvious answer!

• Take a look at DateString documentation, unless I missed the point you can specify it directly. – Kuba Sep 4 '18 at 13:18
• Thanks @Kuba. That didn't work but I found another way, much simpler than I thought. I will post my answer shortly. – pdini Sep 4 '18 at 13:56
• if you want to compare those entires - does it really make sense to add the 4 digits? I doubt that you will find a relevant number of coincidences that way, wouldn't it make more sense to remove the 4 digits from the other dataset? – Albert Retey Sep 5 '18 at 9:20
• @AlbertRetey sure, that would be another way to do it... I would not know where to start to do that, however, and anyway I had started thinking about padding the missing zeros so I stuck with that. – pdini Sep 5 '18 at 10:44
• please see my answer, I don't know your usecase but I would not be surprised if you will find padding with zeros gives strange results at some point... – Albert Retey Sep 5 '18 at 19:51

DateString[
{2016, 5, 30, 11, 58, 46.84}
, {"ISODate", " ", "Time", ".", "Millisecond", "000"}
]

"2016-05-30 11:58:46.840000"

• Very cool! I see what you mean now. I had trouble with NumberForm vs. String vs. Integer formats. DateString seemed able to take String as input, but not NumberForm. In the end I came up with the clunky solution below. Do you see any problems with it? I paid special attention to zero padding on the left for single-digit hours and days. Your spec probably takes care of that automatically? Thanks – pdini Sep 4 '18 at 14:30
• I have MMA 10.0 whereas ISODate came out with 10.2. So your solution does not work for me and I ended up using mine. However, your solution is a lot nicer so I accepted it. – pdini Sep 4 '18 at 21:52
• @pdini: for older versions you could always use DateString[datestr,{"Year","-","Month","-","Day"," ","Time",".","Millisecond","0000"}] – Albert Retey Sep 5 '18 at 19:58

As mentioned in my comment I think in most usecases that I can think of it would make more sense to round the more precise data instead of padding the less precise data if you want to compare them. For example 46.84 from the file with two digits could be either 46.839999 or 46.840001, if you compare that to 46.84000 from the file with 6 digits, which of them would then be larger? If you round both to two digits then you would get the only reasonable result that up to the known precision (of the less precise of the two) they are equal. If you pad with zeros you might introduce an order between them which is not backed by the data.

The conversion would then have to be done to the file with the seconds given to 6 decimals instead of that with the 2 decimals. Once you have extracted the date-strings you could do something like:

datestr = "2016-05-30 11:58:46.846753";

Apply[
StringRiffle[{#1, #2,
ToString@Round[InternalStringToDouble[#3], 0.01]}, ":"] &,
StringSplit[datestr, ":"]
]


to round them to two decimals and then write back. It doesn't look any more difficult to me than converting the file with two decimals...

DateString seems too constrained in the output format, so I built up what I wanted using StringJoin (duh):

x = 2016-08-23 08:45:01.425
y = DateList[x]
z = y[[6]]
w = ToString[NumberForm[z, {5, 6}]]
y[[6]] = StringTake["0" <> w, -9]
y
zz = StringJoin[{ToString[y[[1]]], "-",
ToString[ IntegerString[y[[2]], 10, 2] ], "-",
ToString[ IntegerString[y[[3]], 10, 2] ], " ",
ToString[ IntegerString[y[[4]], 10, 2] ], ":",
ToString[ IntegerString[y[[5]], 10, 2] ], ":",
ToString[y[[6]]]}]


whose output is:

2016-08-23 08:45:01.425
{2016, 8, 23, 8, 45, 1.425}
1.425
1.425000
01.425000
{2016, 8, 23, 8, 45, 01.425000}
2016-08-23 08:45:01.425000
`

Note: I used Constructing a list that includes a leading zero (01,02,03 ... 55, 56, etc.) for the IntegerString command. I found the StringTake command in (coincidentally) Kuba's answer to this question: How do I pad numbers to the left so that all are the same length?