Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Epoch time conversion in CSV

by theneil (Novice)
on Sep 25, 2012 at 15:30 UTC ( #995574=perlquestion: print w/replies, xml ) Need Help??
theneil has asked for the wisdom of the Perl Monks concerning the following question:

Perl Gods,

I am generating a CSV file with the first 2 columns in the Epoch time format. I want to be able to run a script that converts and replaces these fields with a human readable date. How would I got about doing that - I'm just learning Perl so if you could explain it to me like I'm 5 that'd be great haha!


1345752662, 1345752673, CLOSED, CRITICAL, Other fields etc

What I want:

Thu Aug 23 2012 15:11:02, Thu Aug 23 2012 15:11:13, CLOSED, CRITICAL, +ETC
I'm in the GMT-5 timezone and am not sure how to do that part either. Thanks in advance!


Here is the solution I came up with thanks to you guys!

#!/usr/bin/perl use strict; use Text::CSV; use Tie::File; my (@records, @removed); tie @records, 'Tie::File', "test.csv"; @removed = splice(@records,0,2); print join ("\n", @records); untie @records; my $file = 'test.csv'; my $csv = Text::CSV->new(); open (CSV, "<", $file) or die $!; open (MYFILE, '>>convertedDate.csv'); #Column Headers #First 2 are in Epoch time print MYFILE "Arrival, Modified Date, Severity, Status, TicketID, Mess +age\n"; close (MYFILE); while (<CSV>) { if ($csv->parse($_)) { open (MYFILE, '>>convertedDate.csv'); my @columns = $csv->fields(); #I know this next line isn't so elegant print MYFILE scalar(localtime(@columns[0])) . "," . scalar(local +time(@columns[1])) . ",\"" . @columns[2] . "\",\"" . @columns[3] . "\ +",\"" . @columns[4] . "\",\"" . @columns[5] . "\""; print MYFILE "\n"; close (MYFILE); } else { my $err = $csv->error_input; print "Failed to parse line: $err"; } } close CSV;

Replies are listed 'Best First'.
Re: Epoch time conversion in CSV
by tobyink (Abbot) on Sep 25, 2012 at 15:44 UTC

    For parsing and writing CSV, Text::CSV is your friend. And for datetime manipulations, you want DateTime.

    use Text::CSV; use DateTime; use constant DATE_FORMAT => '%a %b %d %Y %T'; my $in_fh = \*DATA; my $out_fh = \*STDOUT; my $csv = Text::CSV->new({ allow_whitespace => 1 }); while (my $row = $csv->getline($in_fh)) { for my $col (0, 1) { my $dt = DateTime->from_epoch( epoch => $row->[$col], time_zone => '-05:00', ); $row->[$col] = $dt->strftime(DATE_FORMAT); } $csv->print($out_fh, $row); print {$out_fh} "\n"; } __DATA__ 1345752662, 1345752673, CLOSED, CRITICAL, Other fields etc
    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
Re: Epoch time conversion in CSV
by BrowserUk (Pope) on Sep 25, 2012 at 15:50 UTC

    Ostensibly, all you'd need is:

    perl -F, -nle"$_ = localtime( $_ ) for @F[0,1]; print join ',', @F" < +infile > outfile

    See perlrun for the command line switches and localtime() for the function that does the work.

    With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.

    RIP Neil Armstrong

    network sites:
Re: Epoch time conversion in CSV
by Marshall (Abbot) on Sep 25, 2012 at 16:12 UTC
    Might also want to look at Time::Local. This module provides functions that are the inverse of built-in perl functions localtime() and gmtime(). There a number of fancy modules that do time calculations, including Date::Calc, but that is probably more than you need.

    I usually do all logging and database entries in GMT and convert to local time for presentation to users. However in your case, sounds like local time is exactly what you want for some kind of a report and you already have something that is basically GMT based. I would mention that epoch is not an ideal format for data enterchange - there are some O/S'es that use a different "epoch time" than what standard Unix uses, but with Windows Perl ports or standard Unix, you will be ok - that's a detail. I like having some kind of readable format in the logs, etc. rather than some kind of integer (like epoch time) anyway.

    One suggestion to consider is a date/time format like: 2012-02-23 23:05 (YYYY-MM-DD) (HH-MM) with leading zero's and time in 24 hour format. The beauty of a format like 2012-02-23 23:05 is that you can use an alpha compare (lt,gt,eq) to order or sort dates. Sometimes useful for reports. That way you don't have to go back to epoch time for the comparison.

    To force leading zero's, use sprintf or printf with a %02d format spec (adds a leading zero if only one digit). If you are using the format above, you need the leading zero or the sort order won't work out right.

    #!/usr/bin/perl -w use strict; printf "%02d\n", $_ foreach ( 2, 23); __END__ 02 23
    See: Wiki Epoch

    Using a date format like I described about works very well. The boss or the users can import this modified date/time formatted .CSV file into an Excel or other spreadsheet and sort it easily using simple spreadsheet procedures. This is a good thing!

      You should consider using an ISO8601 time format, for all the reasons Marshall suggests. This is the code I usually use:
      use POSIX qw/strftime/; print strftime('%Y-%m-%d %H:%M:%S',gmtime);
      If you want local time use this:
      print strftime('%Y-%m-%d %H:%M:%S',localtime);
Re: Epoch time conversion in CSV
by NetWallah (Canon) on Sep 25, 2012 at 15:35 UTC
    Here is a hint:
    print scalar(localtime(1345752662));

                 I hope life isn't a big joke, because I don't get it.

      How would I loop through the whole document alerts.csv?
            How would I loop through the whole document alerts.csv?

        The same way you'd loop over any other data set.

        while(my $line=<DATA>){ # stuff goes here. }
        If you wanted to convert the fields and write them back to the log file in question I'd suggest you give a look at Tie::File.

        Peter L. Berghold -- Unix Professional
        Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Epoch time conversion in CSV
by blue_cowdawg (Monsignor) on Sep 25, 2012 at 15:44 UTC
        I am generating a CSV file with the first 2 columns in the Epoch time format.

    Here is some sample code:

    #!/usr/bin/perl -w use strict; use Data::Dumper; my $line=<DATA>; chomp $line; my @f=split(',',$line); my $dt1=shift @f; my $dt2=shift @f; printf "%s,%s,%s\n",scalar localtime($dt1),scalar localtime($dt2),join +(",",@f); exit(0); __END__ 1345752662, 1345752673, CLOSED, CRITICAL, Other fields etc
    The working part of this really is the scalar localtime part. There are other ways of doing this, but this is a time honored method that works. Brute force, but it works.

    Here is what perldoc -f localtime says that is pertinate to your situation:

    If EXPR is omitted, "localtime()" uses the current time + (as returned by time(3)). In scalar context, "localtime()" returns the ctime(3) v +alue: $now_string = localtime; # e.g., "Thu Oct 13 04:54 +:34 1994" This scalar value is not locale-dependent but is a Perl builtin. For GMT instead of local time use the "gmtime" builtin.
    Hope this is of some help.

    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Epoch time conversion in CSV
by DrHyde (Prior) on Sep 27, 2012 at 10:06 UTC

    As others have said, you want to use DateTime, in particular the from_epoch constructor.

    But be careful! You say "I'm in the GMT-5 timezone", which shows a fundamental misunderstanding of timezones. That could put you in New York, or it could put you in Lima, which are in different timezones. As I write this, it is 05:50 in New York but it is 04:50 in Lima, because one of them is in "daylight saving" time and the other is on sensible time.

    You should always use timezone names, not offsets, and not abbreviations either as abbreviations like CST are ambiguous (is that CST in north America, or in Cuba, or China, or Australia? They differ by up to fifteen and a half hours!). You must use a timezone name like America/Lima to ensure you always get the correct conversion from epoch time to local time.

    Finally, if you're writing out a data file in local time, always include the time zone. This will let other people in different time zones use the data correctly, but it will also help you use the data correctly. You won't have to remember whether you wrote that particular file in your local time, UTC, or something else.

      I wholeheartedly agree

      until you happen to be using an old system tthat doesn't support named timezones :(

      GOOD $ env TZ="Europe/Dublin" date Fri 28 Sep 2012 12:13:03 IST GOOD $ env TZ="Europe/Amsterdam" date Fri 28 Sep 2012 13:13:14 CEST BAD $ env TZ="Europe/Berlin" date Fri Sep 28 11:14:04 Europe/Berlin 2012 BAD $ env TZ="Europe/Amsterdam" date Fri Sep 28 11:14:12 Europe/Amsterdam 2012 BAD $ env TZ="Europe/Dublin" date Fri Sep 28 11:14:38 Europe/Dublin 2012 BAD $ env TZ=MET-1METDST date Fri Sep 28 13:14:50 METDST 2012 BAD $ env TZ=GMT-5GMTDST date Fri Sep 28 17:14:56 GMTDST 2012

      Enjoy, Have FUN! H.Merijn
        I thought that even then DateTime would support them.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://995574]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2018-11-14 08:58 GMT
Find Nodes?
    Voting Booth?
    My code is most likely broken because:

    Results (165 votes). Check out past polls.