Convert Timestamp in text to Serial Date-Time


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert Timestamp in text to Serial Date-Time
# 8  
Old 02-14-2018
Using GNU awk and assuming -500 is 5 hours 00 mins East of UTC:

Code:
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:
Code:
42654.10762731481372611597

# 9  
Old 02-14-2018
Quote:
Originally Posted by AshBax
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.

Code:
 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?
If date is already adjusting correctly for your required timezone then try this:

Code:
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:

Code:
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

This User Gave Thanks to Chubler_XL For This Post:
# 10  
Old 02-14-2018
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  
Old 02-14-2018
Quote:
Originally Posted by RudiC
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...
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)

Last edited by Chubler_XL; 02-14-2018 at 07:46 PM..
# 12  
Old 02-14-2018
You first said time
Quote:
is in the style of:
2016-10-11 07:01:23.375-500
then, you followed it by saying,
Quote:
... Date format (i.e 42,654.2920446 )..
that is Microsoft Timestamp:

but you gave command:
Code:
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  
Old 02-15-2018
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!)
Code:
 awk -F" "  '{$1/=86400;print}' old_file | new_file

and a 3rd line will take that line and
Code:
awk -F" "  '{$1+=25569 ;print}' old_file | new_file

but still struggling....
notably i want 20 decimal places (to take account of milliseconds)
# 14  
Old 02-15-2018
Quote:
Originally Posted by AshBax
still struggling....
notably i want 20 decimal places (to take account of milliseconds)
Have you tried the suggestions in post #9?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Numeric Time to Readable Timestamp - Perl

I am trying to hit an URL using below command and get the data into an excel sheet. wget --user=<<USERID>> --pass=<<PASSWROD>> http://www.files.thatbelongstome.com/file1 -O test1.xls Next step is to consolidate files from 1 to 10 in a single excel sheet and send to my mail. I am working on... (1 Reply)
Discussion started by: PikK45
1 Replies

2. Shell Programming and Scripting

Convert date in dd mm yyyy format to UNIX timestamp

Hello All, I have a date in DD/MM/YYYY format. I am trying to convert this into unix timestamp. I have tried following: date -d $mydate +%s where mydate = 23/12/2016 00:00:00 I am getting following error: date: extra operand `+%s' Try `date --help' for more information. ... (1 Reply)
Discussion started by: angshuman
1 Replies

3. AIX

convert a specific date to a unix timestamp

hello, i have an AIX5.3 machine and i am writing a script to display some processes. inside the script i want to get the time that the process starts and convert it to a unix timestamp. is there a command that i can use to do that? i search the web but all i found is long scripts and it does... (4 Replies)
Discussion started by: omonoiatis9
4 Replies

4. Shell Programming and Scripting

Check if a date field has date or timestamp or date&timestamp

Hi, In a field, I should receive the date with time stamp in a particular field. But sometimes the vendor sends just the date or the timestamp or correctl the date&timestamp. I have to figure out the the data is a date or time stamp or date&timestamp. If it is date then append "<space>00:00:00"... (1 Reply)
Discussion started by: machomaddy
1 Replies

5. Shell Programming and Scripting

How to convert date and timestamp?

Hi, I have a file file1 having data as below 20110501,070742, ,012345678909,09999999999,68.5, 20110501,070236, ,089375855455,09376383333,374.3, 20110501,070525, ,090345895555,08444233444,206.2, 20110501,230051, ,000934744433,07624262223,480.1, First field is date(YYYYMMDD) and second... (5 Replies)
Discussion started by: vsachan
5 Replies

6. Shell Programming and Scripting

To convert a date(in string format) to unix timestamp

Hi All, I have a string like below. "Mar 31 2009" . I want to convert this to unix time . Also please let me know how to find the unix time for the above string minus one day. For Eg. if i have string "Mar 31 2009" i want to find the unix time stamp of "Mar 30 2009". Thanks in advance,... (11 Replies)
Discussion started by: girish.raos
11 Replies

7. Shell Programming and Scripting

how to convert date time to epoch time in solaris

Hi, Is there any easy way to convert date time(stored in shell variable ) to epoch time in solaris box? As +%s is working on linux but not on solaris, also -d option is not working. Any suggestion please? (6 Replies)
Discussion started by: anshuman0507
6 Replies

8. Shell Programming and Scripting

Convert date to unix time

Hi, I need to convert a date in the format yyyy-mm-dd to unix seconds, shell script or perl would be ok since there is no hour/second, we can assume 12am every day thanks in advance funksen (4 Replies)
Discussion started by: funksen
4 Replies

9. Shell Programming and Scripting

Convert Epoch Time to Standard Date and Time & Vice Versa

Hi guys, I know that this topic has been discuss numerous times, and I have search the net and this forum for it. However, non able to address the problem I faced so far. I am on Solaris Platform and unable to install additional packages like the GNU date and gawk to make use of their... (5 Replies)
Discussion started by: DrivesMeCrazy
5 Replies

10. Shell Programming and Scripting

how to convert from timestamp to date format in tcsh

hello all im looking for fast way to convert timestamp format to date format and vaiseversa in tcsh , can it be done? thanks allot (2 Replies)
Discussion started by: umen
2 Replies
Login or Register to Ask a Question