Change unix time to arrange the data in diurnal and hourly pattern

I have an input file with 9 columns where 6th and 7th columns are for start and end unix time. (The overall span of the timeline is 1290895200 to 1291154399, 3 days) I am looking for a perl script which can take in the unix time to specify the hour of the day, counting the start date as D开发者_如何学运维ay 1 and increasing accordingle. Could this be obtained by checking the timestamps on each row and generate output file with the format below (with rest of the values in columns remaining unchanged only unix time stamp converted to Hour and Day):

Hour Day Col3 Col4 Col5 Col6 ..... 
 0    1  .......  
 1    1  ....... 
upto 
 23   3  .... 

Assuming no spaces within columns, the Perl '-a' (perldoc perlrun) switch will be useful. It splits the input in the array @F automatically when combined with -n or -p.

Given the start timestamp (a Unix time), it is easy enough to replace either column 6 or 7 (or both) with an hour/day relateve to the start timestamp. The issue is 'which did you want at the beginning of the line'?

#!/usr/bin/perl -pa
use strict;
use warnings;
my $start = 1290895200;

my $t6_hh = int(($F[5] - $start) / 3600);
my $t6_dd = int($t6_hh / 24) + 1;
   $t6_hh = int($t6_hh % 24);

my $t7_hh = int(($F[6] - $start) / 3600);
my $t7_dd = int($t7_hh / 24) + 1;
   $t7_hh = int($t7_hh % 24);

printf "%2d/%d - %2d/%d ", $t6_hh, $t6_dd, $t7_hh, $t7_dd;
# Perl prints the input line after this because of the -p option

Using this awk program to generate some data:

awk 'BEGIN{
            for (i = 1290895200; i < 1291154400; i += 3200)
            {
                j = i + rand() * (1291154400 - i);
                k = i + 60 * rand();
                printf "C1 C2 C2 C4 C5 %d %d C8 C9\n", k, j;
            }
        }'

The Perl script above generated the output that follows. Column 6 contains a time in your given timestamp range, incrementing every 3200 seconds and randomly perturbed by up to 60 more seconds. Column 7 contains a random time in the range between the value in column 6 and the end time. The output is prefixed with two hour/day values, one for column 6, one for column 7. Tinker with the formatting to your heart's content.

 0/1 -  0/1 C1 C2 C2 C4 C5 1290895207 1290895202 C8 C9
 0/1 -  6/3 C1 C2 C2 C4 C5 1290898427 1291091834 C8 C9
 1/1 - 15/2 C1 C2 C2 C4 C5 1290901613 1291036283 C8 C9
 2/1 -  5/1 C1 C2 C2 C4 C5 1290904840 1290916542 C8 C9
 3/1 -  2/3 C1 C2 C2 C4 C5 1290908056 1291075378 C8 C9
 4/1 -  6/2 C1 C2 C2 C4 C5 1290911231 1291004467 C8 C9
 5/1 - 12/3 C1 C2 C2 C4 C5 1290914402 1291113831 C8 C9
 6/1 -  9/1 C1 C2 C2 C4 C5 1290917631 1290930259 C8 C9
 7/1 -  2/3 C1 C2 C2 C4 C5 1290920800 1291077580 C8 C9
 8/1 -  8/2 C1 C2 C2 C4 C5 1290924004 1291012338 C8 C9
 8/1 - 11/2 C1 C2 C2 C4 C5 1290927241 1291022052 C8 C9
 9/1 - 22/2 C1 C2 C2 C4 C5 1290930455 1291062330 C8 C9
10/1 - 14/3 C1 C2 C2 C4 C5 1290933631 1291120433 C8 C9
11/1 - 17/1 C1 C2 C2 C4 C5 1290936839 1290956811 C8 C9
12/1 - 13/2 C1 C2 C2 C4 C5 1290940042 1291029190 C8 C9
13/1 - 18/3 C1 C2 C2 C4 C5 1290943245 1291135459 C8 C9
14/1 -  5/2 C1 C2 C2 C4 C5 1290946402 1291000990 C8 C9
15/1 -  8/3 C1 C2 C2 C4 C5 1290949619 1291100349 C8 C9
16/1 -  3/3 C1 C2 C2 C4 C5 1290952845 1291080339 C8 C9
16/1 - 23/3 C1 C2 C2 C4 C5 1290956021 1291152621 C8 C9
17/1 -  7/2 C1 C2 C2 C4 C5 1290959258 1291007421 C8 C9
18/1 -  9/3 C1 C2 C2 C4 C5 1290962445 1291101150 C8 C9
19/1 -  5/3 C1 C2 C2 C4 C5 1290965604 1291088606 C8 C9
20/1 -  5/3 C1 C2 C2 C4 C5 1290968853 1291086031 C8 C9
21/1 - 11/2 C1 C2 C2 C4 C5 1290972026 1291021742 C8 C9
22/1 - 12/3 C1 C2 C2 C4 C5 1290975228 1291112555 C8 C9
23/1 - 10/2 C1 C2 C2 C4 C5 1290978416 1291020248 C8 C9
 0/2 - 17/2 C1 C2 C2 C4 C5 1290981609 1291043680 C8 C9
 0/2 - 23/2 C1 C2 C2 C4 C5 1290984853 1291067313 C8 C9
 1/2 - 19/3 C1 C2 C2 C4 C5 1290988003 1291139292 C8 C9
 2/2 - 19/3 C1 C2 C2 C4 C5 1290991230 1291138839 C8 C9
 3/2 -  2/3 C1 C2 C2 C4 C5 1290994419 1291077006 C8 C9
 4/2 - 23/3 C1 C2 C2 C4 C5 1290997629 1291152305 C8 C9
 5/2 - 16/2 C1 C2 C2 C4 C5 1291000805 1291041679 C8 C9
 6/2 - 21/3 C1 C2 C2 C4 C5 1291004004 1291146543 C8 C9
 7/2 -  3/3 C1 C2 C2 C4 C5 1291007223 1291080904 C8 C9
 8/2 - 19/2 C1 C2 C2 C4 C5 1291010454 1291050299 C8 C9
 8/2 -  5/3 C1 C2 C2 C4 C5 1291013627 1291088188 C8 C9
 9/2 - 21/3 C1 C2 C2 C4 C5 1291016803 1291146278 C8 C9
10/2 - 15/3 C1 C2 C2 C4 C5 1291020046 1291122347 C8 C9
11/2 - 17/3 C1 C2 C2 C4 C5 1291023207 1291131809 C8 C9
12/2 - 13/2 C1 C2 C2 C4 C5 1291026441 1291028431 C8 C9
13/2 - 19/3 C1 C2 C2 C4 C5 1291029637 1291137957 C8 C9
14/2 - 15/3 C1 C2 C2 C4 C5 1291032843 1291122324 C8 C9
15/2 - 23/3 C1 C2 C2 C4 C5 1291036053 1291154335 C8 C9
16/2 - 23/2 C1 C2 C2 C4 C5 1291039218 1291066064 C8 C9
16/2 -  3/3 C1 C2 C2 C4 C5 1291042430 1291081713 C8 C9
17/2 - 11/3 C1 C2 C2 C4 C5 1291045650 1291109913 C8 C9
18/2 -  6/3 C1 C2 C2 C4 C5 1291048850 1291092315 C8 C9
19/2 -  3/3 C1 C2 C2 C4 C5 1291052024 1291079578 C8 C9
20/2 - 11/3 C1 C2 C2 C4 C5 1291055228 1291108500 C8 C9
21/2 -  4/3 C1 C2 C2 C4 C5 1291058410 1291085972 C8 C9
22/2 -  2/3 C1 C2 C2 C4 C5 1291061634 1291075865 C8 C9
23/2 - 19/3 C1 C2 C2 C4 C5 1291064801 1291136695 C8 C9
 0/3 - 12/3 C1 C2 C2 C4 C5 1291068029 1291114176 C8 C9
 0/3 - 22/3 C1 C2 C2 C4 C5 1291071244 1291150686 C8 C9
 1/3 - 14/3 C1 C2 C2 C4 C5 1291074453 1291118766 C8 C9
 2/3 - 15/3 C1 C2 C2 C4 C5 1291077650 1291125588 C8 C9
 3/3 -  6/3 C1 C2 C2 C4 C5 1291080812 1291092558 C8 C9
 4/3 - 18/3 C1 C2 C2 C4 C5 1291084007 1291134315 C8 C9
 5/3 -  7/3 C1 C2 C2 C4 C5 1291087216 1291093314 C8 C9
 6/3 -  6/3 C1 C2 C2 C4 C5 1291090424 1291090591 C8 C9
 7/3 -  7/3 C1 C2 C2 C4 C5 1291093642 1291095234 C8 C9
 8/3 - 23/3 C1 C2 C2 C4 C5 1291096814 1291150822 C8 C9
 8/3 - 11/3 C1 C2 C2 C4 C5 1291100019 1291109840 C8 C9
 9/3 - 22/3 C1 C2 C2 C4 C5 1291103239 1291148613 C8 C9
10/3 - 12/3 C1 C2 C2 C4 C5 1291106440 1291113616 C8 C9
11/3 - 16/3 C1 C2 C2 C4 C5 1291109623 1291126884 C8 C9
12/3 - 18/3 C1 C2 C2 C4 C5 1291112808 1291133589 C8 C9
13/3 - 19/3 C1 C2 C2 C4 C5 1291116050 1291138547 C8 C9
14/3 - 19/3 C1 C2 C2 C4 C5 1291119257 1291139971 C8 C9
15/3 - 20/3 C1 C2 C2 C4 C5 1291122408 1291140196 C8 C9
16/3 - 23/3 C1 C2 C2 C4 C5 1291125624 1291153919 C8 C9
16/3 - 17/3 C1 C2 C2 C4 C5 1291128833 1291132430 C8 C9
17/3 - 19/3 C1 C2 C2 C4 C5 1291132029 1291137647 C8 C9
18/3 - 21/3 C1 C2 C2 C4 C5 1291135257 1291144109 C8 C9
19/3 - 20/3 C1 C2 C2 C4 C5 1291138411 1291140416 C8 C9
20/3 - 21/3 C1 C2 C2 C4 C5 1291141637 1291145686 C8 C9
21/3 - 21/3 C1 C2 C2 C4 C5 1291144839 1291146016 C8 C9
22/3 - 23/3 C1 C2 C2 C4 C5 1291148048 1291151978 C8 C9
23/3 - 23/3 C1 C2 C2 C4 C5 1291151228 1291151993 C8 C9

The localtime function gives you a breakdown of a unix timestamp into a list of data containing, among others, the hour and the number of the day within the current year.

#  0    1    2     3     4    5     6     7     8
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(your_timestamp);
             ^                            ^

You can use that to build your format pretty easily.

offset = date - start_date
day = offset / (24*60*60)
hour = (offset % (24*60*60)) / (60*60)

if start_date is 1290895200 and date is 1291154399 then offset is 259199, day is 2 and hour is 23

Have a look at DateTime module which will most probably be of help to you.

#!/usr/bin/env perl

use strict;
use warnings;

use DateTime;

my $time1 = 1290895200;
my $time2 = 1291154399;

my $dt1 = DateTime->from_epoch( 'epoch' => $time1 );
my $dt2 = DateTime->from_epoch( 'epoch' => $time2 );

my $duration = $dt1->subtract_datetime($dt2);
print 'Days: ',  $duration->days,  "\n";
print 'Hours: ', $duration->hours, "\n";