Convert Numeric Time to Readable Timestamp - Perl


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert Numeric Time to Readable Timestamp - Perl
# 1  
Old 07-11-2017
Linux 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.

Code:
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 the Perl script for the same.

Now, I need to convert the below data to readable format in the same Perl. How to achieve that?

Actual Data
Code:
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400

To be Data
Code:
GMT excel datetime	Value
07/11/2017 11:02:45	2,223,003,680
07/11/2017 11:02:50	2,223,323,176
07/11/2017 11:02:55	2,225,181,648
07/11/2017 11:03:00	2,225,843,160
07/11/2017 11:03:05	2,227,239,952
07/11/2017 11:03:10	2,227,508,248
07/11/2017 11:03:15	2,229,306,008
07/11/2017 11:03:20	2,229,592,736
07/11/2017 11:03:25	2,230,992,288
07/11/2017 11:03:30	2,231,824,880
07/11/2017 11:03:35	2,233,473,560
07/11/2017 11:03:40	2,233,610,200
07/11/2017 11:03:45	2,234,898,400

# 2  
Old 07-12-2017
Quote:
Originally Posted by PikK45
...
...
I need to convert the below data to readable format in the same Perl. How to achieve that?

Actual Data
Code:
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400

To be Data
Code:
GMT excel datetime    Value
07/11/2017 11:02:45    2,223,003,680
07/11/2017 11:02:50    2,223,323,176
07/11/2017 11:02:55    2,225,181,648
07/11/2017 11:03:00    2,225,843,160
07/11/2017 11:03:05    2,227,239,952
07/11/2017 11:03:10    2,227,508,248
07/11/2017 11:03:15    2,229,306,008
07/11/2017 11:03:20    2,229,592,736
07/11/2017 11:03:25    2,230,992,288
07/11/2017 11:03:30    2,231,824,880
07/11/2017 11:03:35    2,233,473,560
07/11/2017 11:03:40    2,233,610,200
07/11/2017 11:03:45    2,234,898,400

As per this webpage: DATEVALUE function - Office Support
the number in the "date" column in Microsoft Excel is the number of days since 1/1/1900.
Which means that if you have a date in a cell in MS Excel and you format the cell to a Number and you see 42927.46024305555 then the date in that cell is 42927.46024305555 days after 1/1/1900.

We can use the Time::Piece core module to work backwards using the logic above.
An attempt is posted below.
I couldn't make it to work from "1/1/1900" so I've adjusted accordingly.
Check the comments.

Code:
$
$ cat data.txt
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400
$
$ perl -lne 'BEGIN {
                 # Use core module Time::Piece to set up the epoch. I am unable
                 # to use dates less than "12/17/1902" as the epoch due to an
                 # error in the module. Microsoft Excel uses "1/1/1900" as its
                 # epoch. So I convert using "1/1/1903" as epoch and then subtract
                 # $delta days from the result.
                 use Time::Piece;
                 $epoch = Time::Piece->strptime("1-1-1903","%m-%d-%Y");
                 $delta = 365 * 3 + 2
             }
             if ($. == 1) {
                 # From the header line, get the width of the first column.
                 $len = index($_, "Value");
                 $fmt = "%-${len}s%s\n";
                 print;
                 next
             }
             # Parse the data to retrieve the MS Excel date and the value.
             # Perform date arithmetic. You can only add seconds to a Time::Piece
             # object using "+" operator. So convert days to seconds by multiplying
             # with 24*60*60.
             ($dt, $val) = m/^(\S+)\s+(\S+)$/;
             $fmt_dt = $epoch + ($dt - $delta)*24*60*60;
             printf($fmt, $fmt_dt->strftime("%m/%d/%Y %H:%M:%S"), $val);
            ' data.txt
GMT excel datetime      Value
07/11/2017 11:02:45     2,223,003,680
07/11/2017 11:02:50     2,223,323,176
07/11/2017 11:02:54     2,225,181,648
07/11/2017 11:03:00     2,225,843,160
07/11/2017 11:03:04     2,227,239,952
07/11/2017 11:03:10     2,227,508,248
07/11/2017 11:03:15     2,229,306,008
07/11/2017 11:03:19     2,229,592,736
07/11/2017 11:03:25     2,230,992,288
07/11/2017 11:03:29     2,231,824,880
07/11/2017 11:03:35     2,233,473,560
07/11/2017 11:03:40     2,233,610,200
07/11/2017 11:03:45     2,234,898,400
$
$


Last edited by durden_tyler; 07-12-2017 at 04:41 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Timestamp in text to Serial Date-Time

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... (14 Replies)
Discussion started by: AshBax
14 Replies

2. Shell Programming and Scripting

AIX : Need to convert UNIX Timestamp to normal timestamp

Hello , I am working on AIX. I have to convert Unix timestamp to normal timestamp. Below is the file. The Unix timestamp will always be preceded by EFFECTIVE_TIME as first field as shown and there could be multiple EFFECTIVE_TIME in the file : 3.txt Contents of... (6 Replies)
Discussion started by: rahul2662
6 Replies

3. Shell Programming and Scripting

Convert UNIX timestamp to readable format in the file

Hello I have a file : file1.txt with the below contents : 237176 test1 test2 1442149024 237138 test3 test4 1442121300 237171 test5 test7 1442112823 237145 test9 test10 1442109600 In the above file fourth field represents the timestamp in Unix format. I found a command which converts... (6 Replies)
Discussion started by: rahul2662
6 Replies

4. Shell Programming and Scripting

Convert epoch time stamp into human readable format

Can someone help me to write a shell script to convert epoch timestamp into human readable format 1394553600,"test","79799776.0","19073982.728571","77547576.0","18835699.285714" 1394553600,"test1","80156064.0","19191275.014286","62475360.000000","14200554.720000"... (10 Replies)
Discussion started by: Moon1234
10 Replies

5. Shell Programming and Scripting

How to get time duration between two human readable time stamp in Unix?

Here is two time I have: Jul 12 16:02:01 Jul 13 01:02:01 and how can I do a simple match to get difference between two time which is 09:00:00 Thanks in advance. (3 Replies)
Discussion started by: ford99
3 Replies

6. Shell Programming and Scripting

Convert epoch to human readable date & time format

Hello I have log file from solaris system which has date field converted by Java application using System.currentTimeMillis() function, example is 1280943608380 which equivalent to GMT: Wed, 04 Aug 2010 17:40:08 GMT. Now I need a function in shell script which will convert 1280943608380... (3 Replies)
Discussion started by: Yaminib
3 Replies

7. Shell Programming and Scripting

PERL:How to convert numeric values txt file to PACKED DECIMAL File?

Is there any way to convert numeric values txt file to PACKED DECIMAL File using PERL. Regards, Alok (1 Reply)
Discussion started by: aloktiwary
1 Replies

8. UNIX for Dummies Questions & Answers

Unix timestamp to readable date

How would I convert a unix timestamp such as "1232144092" to a readable date such as "1/16/2009 10:14:28 PM" ? I thought I could use date, but I don't think so now.. Any help would be great!! (4 Replies)
Discussion started by: Rhije
4 Replies

9. Programming

converting unix timestamp into readable format using c++

hi everyone, im new here and am in desperate need of help. I want to convert my 32 bit unix time stamp ' 45d732f6' into a readable format (Sat, 17 February 2007 16:53:10 UTC) using c++. I have looked around the interent but i just cant make sense of anything. All examples i can find just... (3 Replies)
Discussion started by: uselessprog
3 Replies

10. Shell Programming and Scripting

Perl code to differentiate numeric and non-numeric input

Hi All, Is there any code in Perl which can differentiate between numeric and non-numeric input? (11 Replies)
Discussion started by: Raynon
11 Replies
Login or Register to Ask a Question