3
$\begingroup$

I have a series of dates that regularly change format (24 hrs/12 hrs). Is there an easy way to parse this data?

dates={"12/31/2014 11:49:23 PM", "1/01/2015 4:15", "1/01/2015 6:21", "1/01/2015 6:32", "1/01/2015 16:32"}

I've used this code;

AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute"}}] & /@dates

but it has issues with the AM/PM dates. It there an easy/clean way to try to parse the date as 12 hr time and if that doesn't work try 24 hrs.

$\endgroup$
2
$\begingroup$

Update: A cleaner version of atF below:

atF2 = If[StringFreeQ[#, "PM"|"AM"], 
    AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute"}}], 
    AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute", "Second", "AMPM"}}]] &;
atF2 /@ dates
(* {3627157763, 3629074500, 3629082060, 3629082720, 3629118720} *)

Original post:

DateList can parse mixed date strings properly:

dates = {"12/31/2014 11:49:23 PM", "1/01/2015 4:15", "1/01/2015 6:21",
         "1/01/2015 6:32", "1/01/2015 16:32"};

DateList /@ dates
(* {{2014, 12, 31, 23, 49, 23.}, {2015, 1, 1, 4, 15, 0.}, {2015, 1, 1, 6,
    21, 0.}, {2015, 1, 1, 6, 32, 0.}, {2015, 1, 1, 16, 32,  0.}} *)

AbsoluteTime /@ DateList /@ dates
(* {3629058563, 3629074500, 3629082060, 3629082720, 3629118720} *)

Or, you can do

atF= Quiet@Check[AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute"}}], 
  AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute", "Second", "AMPM"}}]]&; 
 atF/@ dates
 (* {3629058563, 3629074500, 3629082060, 3629082720, 3629118720} *)
$\endgroup$
  • $\begingroup$ True, the DateList does seem to work. I've been avoiding DateList because I found AbsoluteTime to be much faster. For my current application it doesn't seem to be an issue (only 1655 dates). $\endgroup$ – Cam Mar 19 '15 at 22:50
  • $\begingroup$ One issue I found when parsing my complete data was that I get a "date is ambiguous" warning when parsing "12/09/2014 23:06" even though the final date seems fine. Is this an issue for this approach generally? $\endgroup$ – Cam Mar 19 '15 at 22:55
  • $\begingroup$ The second approach is a little faster than the first with my data. 1.07s vs 0.87s. $\endgroup$ – Cam Mar 19 '15 at 23:01
  • $\begingroup$ @Cam, Re "ambiguous date" warning, the only way to to fix it is to provide the date pattern {"Month", "Day", "Year", ...} or {"Day", "Month", "Year", ...}, but then we get into a problem with mixed hour format in the data (some times in 12hr format some in 24hr). If the final result is fine, then just using Quiet to suppress the warning message is the best approach. Using AbsoluteTime directly on date strings is better than processing the data twice. $\endgroup$ – kglr Mar 19 '15 at 23:31
2
$\begingroup$

One way:

fixed = StringReplace[
   dates, {" " ~~ h__ ~~ ":" ~~ m__ ~~ ":" ~~ s__ ~~ " PM" :> 
     " " <> ToString[ToExpression[h] + 12] <> ":" <> m, 
    " " ~~ h__ ~~ ":" ~~ m__ ~~ ":" ~~ s__ ~~ " AM" :> 
     " " <> h <> ":" <> m}];

AbsoluteTime[{#, {"Month", "Day", "Year", "Hour", "Minute"}}] & /@ fixed

(* {3629058540,3629074500,3629082060,3629082720,3629118720,3629015340} *)

(I assumed seconds are not of interest, since not in your absolute time conversion and that the AM/PM all have all components)

$\endgroup$
  • $\begingroup$ This approach seems the fastest with my data - 0.42s. $\endgroup$ – Cam Mar 19 '15 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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