Epoch time conversion in CSV

[theneil]

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!

CSV:

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!

***************UPDATE:*****************

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;

[tobyink]

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

[BrowserUk]

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.

network sites:

[Marshall]

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

update:
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!

[hokey]

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);

[NetWallah]

Here is a hint:

print scalar(localtime(1345752662));

[theneil]

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

[blue_cowdawg]

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

Dude!,
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.

[blue_cowdawg]

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.

[DrHyde]

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.

[Tux]

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

[DrHyde]

I thought that even then DateTime would support them.