Convert Timestamp in text to Serial Date-Time

Shell Programming and Scripting
1

Hi,
I have a data file where the timestamp is in the style of:
2016-10-11 07:01:23.375-500

which is yyyy-mm-dd hh-mm-ss-000 then time conversion from UTC

What i need to do is convert these timestamps from the above format to a the Serial Date format (i.e 42,654.2920446 )

now.. if really would be allowed to push my luck, i would like this format specified to 20 decimal places, AND use the "-500" to convert back to UTC

any help would be greatly appreciated.

2

Get it by Date format converter

3

Forgive my ignorance, but what is this Serial Data format? Or do you mean Serial Date format? Googling the former comes up with nothing appropriate; googling the latter comes up with references to Excel and Matlab.

My best guess is you want to convert to the number of days since a certain date. What is that date?

Andrew

4

Thanks Andrew.
I did indeed mean serial date (i've just edited my original question).
What I'm looking for is the date used in excel.

so really converting any of those data/timestamp strings in my original file to the equivalent excel timestamp.

regards

5

OP means Microsoft Timestamp
get it : Date format converter

6

many thanks
will speak with them.

---------- Post updated at 03:23 PM ---------- Previous update was at 01:57 PM ----------

i have code that converts from the data format mentioned into epoch.

 awk -F, '{"date +%s -d\""$1 "\""| getline dte;$1=dte}1' OFS="-500," databars_example_2.csv > databars_example_222.csv

now i just need to change epoch into serial data..... any thoughts?

7

If you need the date in EXCEL, why not have EXCEL read and interpret the date string?

8

Using GNU awk and assuming -500 is 5 hours 00 mins East of UTC:

echo "2016-10-11 07:01:23.375-500" | TZ=UTC0 gawk '{
  datespec=$0
  gsub(/[-:]/," ", datespec)
  sub(/[.].*/,"", datespec)

  secs=$0
  sub(/[+-].*/, "", secs)
  sub(/.*[.]/,"0.", secs)

  tz = gensub(/.*([+-])/,"\\1","g")
  tzadj = int(tz / 100) * 3600 + (tz % 100) * 60
  UnixTime = mktime(datespec) + secs + tzadj
  Excel = (UnixTime / 86400) + 25569 
  printf "%0.20f\n", Excel
}'

result:

42654.10762731481372611597
9

If date is already adjusting correctly for your required timezone then try this:

awk -F\, '{
  secs=$1
  sub(/[+-].*/, "", secs)
  sub(/.*[.]/,"0.", secs)

  "date +%s -d\""$1"\"" | getline datespec

  UnixTime = datespec + secs
  Excel = (UnixTime / 86400) + 25569 
  printf "%0.20f\n", Excel
}' databars_example_2.csv

Otherwise calculating timezone adjust in awk:

awk -F, '{
  
  secs=$1
  sub(/[+-].*/, "", secs)
  sub(/.*[.]/,"0.", secs)

  tz = $1
  sub(/.*[+]/,"",tz)
  sub(/.*[-]/,"-",tz)
  tzadj = int(tz / 100) * 3600 + (tz % 100) * 60

  tstamp = $1
  gsub(/[-+][0-9]*$/,"",tstamp)

  "date -u +%s -d\""tstamp"\"" | getline datespec

  UnixTime = datespec + secs + tzadj
  Excel = (UnixTime / 86400) + 25569
  printf "%0.20f\n", Excel
}' databars_example_2.csv
1 Like
10

Nice!
But, help me out - how did you derive the 25569 "EXCEL correction summand"? That would represent 03.01.70 00:00 (Jan 3rd), NOT the epoch 1970/01/01 01:00:00 , which in turn would be 25567.0416666667 - IF (!) EXCEL's date base were set to 01/01/1900, not the other (more usual?) 01/01/1904.

That's why I proposed to have EXCEL interpret the string so all the local setting were taken into account...

11

I used the worked example in the OP that stated:

2016-10-11 07:01:23.375 = 42,654.2920446

So I knew that epoch date used in the OPs system was 01-01-1900, and not the Excel 2008 for OSX, default of 01-01-1904, which I believe requires 24107 (not 25567.0416666667)

12

You first said time

then, you followed it by saying,

that is Microsoft Timestamp:

but you gave command:

awk -F, '{"date +%s -d\""$1 "\""| getline dte;$1=dte}1' OFS="-500," databars_example_2.csv > databars_example_222.csv

that's for Unix Timestamp:

do you think you are going to achieve your purpose by asking a vague question ?

13

I have other files already in this format, so I want to keep the formats the same.
In addition, i don't want to risk opening it in excel and seeing the formats change.

i've added a second line that (is getting closer but certainly no cigar in sight!)

 awk -F" "  '{$1/=86400;print}' old_file | new_file

and a 3rd line will take that line and

awk -F" "  '{$1+=25569 ;print}' old_file | new_file

but still struggling....
notably i want 20 decimal places (to take account of milliseconds)

14

Have you tried the suggestions in post #9?

15

thanks for all the suggestions... will carry on working on it today and will let you know how i got on.