1
$\begingroup$

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!

Improve this question
$\endgroup$
5
  • $\begingroup$ Take a look at DateString documentation, unless I missed the point you can specify it directly. $\endgroup$
    – Kuba ♦
    Sep 4, 2018 at 13:18
  • $\begingroup$ Thanks @Kuba. That didn't work but I found another way, much simpler than I thought. I will post my answer shortly. $\endgroup$
    – pdini
    Sep 4, 2018 at 13:56
  • $\begingroup$ 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? $\endgroup$
    – Albert Retey
    Sep 5, 2018 at 9:20
  • $\begingroup$ @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. $\endgroup$
    – pdini
    Sep 5, 2018 at 10:44
  • $\begingroup$ 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... $\endgroup$
    – Albert Retey
    Sep 5, 2018 at 19:51

3 Answers 3

Reset to default
3
$\begingroup$

What about:

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

"2016-05-30 11:58:46.840000"
Improve this answer
$\endgroup$
3
  • $\begingroup$ 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 $\endgroup$
    – pdini
    Sep 4, 2018 at 14:30
  • $\begingroup$ 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. $\endgroup$
    – pdini
    Sep 4, 2018 at 21:52
  • $\begingroup$ @pdini: for older versions you could always use DateString[datestr,{"Year","-","Month","-","Day"," ","Time",".","Millisecond","0000"}] $\endgroup$
    – Albert Retey
    Sep 5, 2018 at 19:58
0
$\begingroup$

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...

Improve this answer
$\endgroup$
-1
$\begingroup$

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?

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.