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

Question

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 Day 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  ....

real example input and the example of wanted output would be helpful — clt60, Jul 10 '11 at 17:19
1290895200 = Sat Nov 27 22:00:00 2010 in UTC (14:00 in US/Pacific). Presumably, if this was midnight in your time zone, you are in eastern Europe in time zone UTC+2 (+02:00). — Jonathan Leffler, Jul 10 '11 at 20:35
You have a start and end time for each row, but which do you want to appear in the new hour/day columns? The start time, or the end time? — Jonathan Leffler, Jul 10 '11 at 20:38
Yes, I am in eastern European time zone. I would want start time to appear in the new hour/day columns. — user744121, Jul 11 '11 at 07:07

Jonathan Leffler · Accepted Answer · 2011-07-27T14:30:08.957

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

@Johathan: Sorry for such a delayed reply. I ran this script as 'perl script.pl < input.txt > output.txt' and this returns me error:"my" variable $t6_hh masks earlier declaration in same scope at time.pl line 10. However, I get the output. Thanks for that. "my" variable $t7_hh masks earlier declaration in same scope at time.pl line 14. — user744121, Jul 27 '11 at 11:09
This works well. Now if I want to aggregate C8 for hour/day, how would I do it. Say, if C8 has traffic volume ( in bytes), then I want calculate total traffic volume for particular hour and day. — user744121, Jul 27 '11 at 12:03
@user744121: yes - I expect that when I tested, I fixed the duplicated `my` problems and forgot to fix the code in the answer. I'll remove those. — Jonathan Leffler, Jul 27 '11 at 14:29
@user744121: if you want to aggregate, you have to decide how to split a C8 value that spreads across multiple hours (as shown in my example data - but not necessarily in your real data). The second line of data spreads over 55 hours, for example. Once you know how that is to be handled, then you can process the data in several ways. I'd probably generate values for each hour with an appropriate derived/split value in two new columns: C10 containing the day/hour value, and C11 containing the apportioned C8 value. [...continued...] — Jonathan Leffler, Jul 27 '11 at 14:47
[...continued..] Then you can have a process which reads and sorts the data, and does 'control-break' reporting. Basically, you order on C10; when you get a new C10 value, you print out the previous accumulated values (if any) and rezero your aggregates. You then accumulate the data. Finally, at the end, you print out the last aggregate (if there is one). This is a basic report generation technique. — Jonathan Leffler, Jul 27 '11 at 14:49

score 0 · Answer 2 · answered Jul 10 '11 at 17:11

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.

Alan Haggai Alavi · Answer 3 · 2011-07-10T18:03:54.567

0

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

edited Jul 10 '11 at 18:03

answered Jul 10 '11 at 17:11

Alan Haggai Alavi

72,802
19
102
127

My question was to make the start time (1290895200) a reference and compare all other start times with that reference and determine if its Day1 or Day 2 or Day3 – user744121 Jul 10 '11 at 17:35

score 0 · Answer 4 · answered Jul 10 '11 at 17:19

0

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

answered Jul 10 '11 at 17:19

Dan D.

73,243
15
104
123

Yes, thats correct. But I need a way to form a table out of this as specified in the question. – user744121 Jul 10 '11 at 17:37

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

4 Answers4