convert Timestamp to actual date

Share icon

I need to convert an entire text wave of time recorded as follow:
"2011-08-30 19:11:00"
into a time in sec or whatever other time unit.
The quotes are part of the text recorded.

This is what i have tried so far, originally based on a code i found online, unsuccessful:


Function TextDateToDateYYYYMMDD_hhmmss(dateStr)
wave/t dateStr // In format YYYY-MM-DD hh:mm

Variable year, month, day,hour,minute,second
Variable i,dt
string inter

Make/o/n=(numpnts(dateStr)) NewWave

for (i=0;i<=numpnts(dateStr);i=+1)

inter = (datestr[i])
sscanf "%4d%*[-]%2d%*[-]%2d%*[ ] %2d%*[:] %2d%*[:]%2d" year, month, day,hour,minute,second

dt = date2secs(year, month, day ) + hour*3600+minute*60 +second
NewWave[i]=dt
endfor
return NewWave
End
Here's a function where I've abstracted just the part with sscanf:
Function test(instring)
    String instring
   
    Variable year, month, day,hour,minute,second
    sscanf instring, "\"%4d%*[-]%2d%*[-]%2d%*[ ] %2d%*[:] %2d%*[:]%2d\"", year, month, day,hour,minute,second
   
    return  date2secs(year, month, day ) + hour*3600+minute*60 +second
end

There are a couple things to note: I added instring to the beginning of the sscanf command; that's the source text. I added a comma after the format string. I added backslash-quotes at the start and end of the format string since you said the quotes are actually part of the text in the text wave.

You might be able to get LoadWave to do this directly if you use a custom date format:

DisplayHelpTopic "LoadWave"

and read the section about the /R flag.

John Weeks
WaveMetrics, Inc.
support@wavemetrics.com

September 19, 2011 at 04:57 pm - Permalink

I use a function identical to johnweeks's to convert the timestamps from Campbell Scientific files into Igor date/time.

A minor tweak to the sscanf format string will permit reading sub-second values too. Change the input format for seconds from 'd' (integer) to 'f' (floating point):

Function test(instring)
    String instring
   
    Variable year, month, day,hour,minute,second
    sscanf instring, "\"%4d%*[-]%2d%*[-]%2d%*[ ] %2d%*[:] %2d%*[:]%5f\"", year, month, day,hour,minute,second
   
    return  date2secs(year, month, day ) + hour*3600+minute*60 +second
end


This function style is handy because you can do your conversion as a simple wave assignment.

Make/N=(numpnts(textTS))/D numericTS   // don't forget /D for time waves
numericTS = test( textTS[p] )

September 22, 2011 at 05:13 pm - Permalink

If you are not dealing with fractional seconds I would emphatically recommend NOT to use double precision numerical waves to store times.
I would store it in an unsigned 32 bit integer wave, much more suitable for storing very large integers than a double precision wave. In some cases (such as the number of ticks since system start) storing such values in a double precision wave results in undesirable truncation.

 make/u/i/o dt

Note that this approach would overflow in ~2039.

p.s. WM, when is there going to be a (signed/unsigned) 64bit integer wave?

September 22, 2011 at 07:44 pm - Permalink

Quote:
I would store it in an unsigned 32 bit integer wave, much more suitable for storing very large integers than a double precision wave.

I don't think that's right. 32-bit unsigned integers can store only 32-bits. Double-precision floating point has a 53-bit significand and therefore can store 53-bit integers without truncation.

According to wikipedia:
Quote:
. . . any integer with absolute value less than or equal to 2^53 can be exactly represented in the double precision format.

September 22, 2011 at 08:20 pm - Permalink

Dear all,

I have hit a snag. In my text wave, I have a time column in the PM/AM format (see attached pic). How can I convert it so that IGOR can recognize it and give me a proper time wave? 

 

Thank you.

 

 
Text Wave (33.33 KB)

August 27, 2019 at 06:59 am - Permalink

This function handles time with fractional seconds and AM/PM:

// ConvertTimeWithAMPMToSecond(timeStr)
// Examples:
//      Print ConvertTimeWithAMPMToSecond("01:50:12.47 AM")     // Prints 6612.47
//      Print Secs2Time(6612.47, 3, 2)                          // Prints 01:50:12.47
//      Print ConvertTimeWithAMPMToSecond("01:50:12.47 PM")     // Prints 49812.5
//      Print Secs2Time(49812.5, 3, 2)                          // Prints 13:50:12.50
Function ConvertTimeWithAMPMToSecond(timeStr)
    String timeStr
   
    Variable hour, minute, second
    String AMPMStr
    sscanf timeStr, "%2d%*[:]%2d%*[:]%g %s", hour, minute, second, AMPMStr
   
    Variable result = hour*3600 + minute*60 + second
    if (CmpStr(AMPMStr,"PM") == 0)
        result += 43200         // 12*60*60
    endif
   
    return result
End

 

August 28, 2019 at 05:15 am - Permalink