Awk: time intervals based on epoch time

Shell Programming and Scripting
1

I have a list of epoch times delimited by "-" as follows:

1335078000 - 1335176700  
1335340800 - 1335527400
1335771300 - 1335945600
1336201200 - 1336218000

The corresponding dates are:

20120422 1000 - 20120423 1325 	
20120425 1100 - 20120427 1450 	
20120430 1035 - 20120502 1100 	
20120505 1000 - 20120505 1440

I would like to create 5 minutes time intervals based on epoch time columns, for each row in the input file.

The script I've came with, does not work properly if I have several days on the same row.

awk -F"-" '
	{
		for (i=$1;i<=$2;i+=300)   #5 minutes time interval
			{
				day = strftime("%j", $1)
				if (strftime("%H%M%S", i) <= strftime("%H%M%S", $2))
					{
						if (i != $1)
							{
								strftime("%H%M%S", i) == strftime("%H%M%S", $1)
								day2 = strftime("%j", $2)
								count++
							}
							
						else	
							{
								if (day == day2)
								         {
								         	count++
								         }
								 else
								 	{
								 		count =1
								 	}
							}
						print strftime("%Y-%m-%d",i),strftime("%H%M%S", i),count
					} 
				else 
					{
						count=1
						$1=i+61200
						i=$1
						print strftime("%Y-%m-%d",i),strftime("%H%M%S", i),count
					}
			}
	}
' input > output.interval

I will use the first row as example to show the script result and desired output

1335078000 - 1335176700    (20120422 1000 - 20120423 1325)

2012-04-22 100000 1
2012-04-22 100500 2
2012-04-22 101000 3
2012-04-22 101500 4
2012-04-22 102000 5
2012-04-22 102500 6
2012-04-22 103000 7
2012-04-22 103500 8
2012-04-22 104000 9
2012-04-22 104500 10
2012-04-22 105000 11
2012-04-22 105500 12
2012-04-22 110000 13
2012-04-22 110500 14
2012-04-22 111000 15
2012-04-22 111500 16
2012-04-22 112000 17
2012-04-22 112500 18
2012-04-22 113000 19
2012-04-22 113500 20
2012-04-22 114000 21
2012-04-22 114500 22
2012-04-22 115000 23
2012-04-22 115500 24
2012-04-22 120000 25
2012-04-22 120500 26
2012-04-22 121000 27
2012-04-22 121500 28
2012-04-22 122000 29
2012-04-22 122500 30
2012-04-22 123000 31
2012-04-22 123500 32
2012-04-22 124000 33
2012-04-22 124500 34
2012-04-22 125000 35
2012-04-22 125500 36
2012-04-22 130000 37
2012-04-22 130500 38
2012-04-22 131000 39
2012-04-22 131500 40
2012-04-22 132000 41
2012-04-22 132500 42
2012-04-23 063000 1	 <<<< here should start from 100000 again
2012-04-23 063500 2
2012-04-23 064000 3
2012-04-23 064500 4
2012-04-23 065000 5
2012-04-23 065500 6
2012-04-23 070000 7
2012-04-23 070500 8
2012-04-23 071000 9
2012-04-23 071500 10
2012-04-23 072000 11
2012-04-23 072500 12
2012-04-23 073000 13
2012-04-23 073500 14
2012-04-23 074000 15
2012-04-23 074500 16
2012-04-23 075000 17
2012-04-23 075500 18
2012-04-23 080000 19
2012-04-23 080500 20
2012-04-23 081000 21
2012-04-23 081500 22
2012-04-23 082000 23
2012-04-23 082500 24
2012-04-23 083000 25
2012-04-23 083500 26
2012-04-23 084000 27
2012-04-23 084500 28
2012-04-23 085000 29
2012-04-23 085500 30
2012-04-23 090000 31
2012-04-23 090500 32
2012-04-23 091000 33
2012-04-23 091500 34
2012-04-23 092000 35
2012-04-23 092500 36
2012-04-23 093000 37
2012-04-23 093500 38
2012-04-23 094000 39
2012-04-23 094500 40
2012-04-23 095000 41
2012-04-23 095500 42
2012-04-23 100000 43
2012-04-23 100500 44
2012-04-23 101000 45
2012-04-23 101500 46
2012-04-23 102000 47
2012-04-23 102500 48
2012-04-23 103000 49
2012-04-23 103500 50
2012-04-23 104000 51
2012-04-23 104500 52
2012-04-23 105000 53
2012-04-23 105500 54
2012-04-23 110000 55
2012-04-23 110500 56
2012-04-23 111000 57
2012-04-23 111500 58
2012-04-23 112000 59
2012-04-23 112500 60
2012-04-23 113000 61
2012-04-23 113500 62
2012-04-23 114000 63
2012-04-23 114500 64
2012-04-23 115000 65
2012-04-23 115500 66
2012-04-23 120000 67
2012-04-23 120500 68
2012-04-23 121000 69
2012-04-23 121500 70
2012-04-23 122000 71
2012-04-23 122500 72
2012-04-23 123000 73
2012-04-23 123500 74
2012-04-23 124000 75
2012-04-23 124500 76
2012-04-23 125000 77
2012-04-23 125500 78
2012-04-23 130000 79
2012-04-23 130500 80
2012-04-23 131000 81
2012-04-23 131500 82
2012-04-23 132000 83
2012-04-23 132500 84

Any help will be much appreciated, and if possible I would like to know if are better ways doing this.

Thanks in advance.

2

You're adding arbitrary 17 hours - why should it advance to 100000 next day (unless you are at 170000)?

Try

awk -F"-" '

function strftime(FMT,SECS)     {("date \"+" FMT "\" -d@" SECS) | getline TMP
                                 return TMP
                                }

                {gsub (/ /,_)
                 day1 = strftime("%j", $1)
                 day2 = strftime("%j", $2) 
                 timespan = ($2-$1)%86400  
                 for (j=day1; j<=day2; j++)
                        {count=1
                         for (i=0; i<=timespan; i+=300)        
                                print strftime("%Y-%m-%d %H%M%S", i+$1),  count++
                         $1+=86400
                        }
                }

'  input
2012-04-22 090000  1
2012-04-22 090500  2
2012-04-22 091000  3
.
.
.
2012-04-22 122000  41
2012-04-22 122500  42
2012-04-23 090000  1
2012-04-23 090500  2
2012-04-23 091000  3
2012-04-23 091500  4

and report back. My timezone seems an hour off, and, unfortunately, my mawk doesn't have the strftime function so I had to fiddle around with date .

2 Likes
3

Nice solution RudiC! You can set TZ for your awk process like this:

TZ=GMT-3 awk -F"-" '
...
1 Like
4

Thank you both of you for the input. Everything worked just fine.