How to convert DateList format to standard SQL format?

Question

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!

Answer 1

What about:

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

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

Answer 2

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[Internal`StringToDouble[#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...

Answer 3

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?