2

I have a file with records in this format :

D20220327,S2927,977,1

D20220328,S2927,977,1

D20220329,S2927,977,1

D20220330,S2927,977,1

D20220331,S2927,977,1

D20220401,S2927,977,1

D20220402,S2927,977,1

D20220403,S2927,977,1

D20220404,S2927,977,1

However after applying the transformation to shift these to 7 days back in the past, it is not working for dates from 28th Mar to 03rd April, However same code logic is working fine on 27th March & 04th April. I am not able to figure why this is not working for one week only. This is the output

D20220320,S2927,977,1 -- correct

D20220320,S2927,977,1 -- incorrect 

D20220321,S2927,977,1 -- incorrect

D20220322,S2927,977,1 -- incorrect

D20220323,S2927,977,1 -- incorrect

D20220324,S2927,977,1 -- incorrect

D20220325,S2927,977,1 -- incorrect

D20220326,S2927,977,1 -- incorrect

D20220328,S2927,977,1 -- correct

Logic used here is :

    BEGIN {
        OFS = FS = ","
}

{
        t = mktime(sprintf("%4d %.2d %.2d 00 00 00",
                substr($1,2,4),
                substr($1,6,2),
                substr($1,8,2)));

        $1 = substr($1,1,1) strftime("%Y%m%d", t - 7*24*60*60)

        print
}

2
  • --GNU Awk 3.1.7 Nov 3, 2021 at 7:47
  • 1
    that gawk version is 11 years out of date and missing a lot of important bug fixes and new features, you should get a newer version. We're currently on version 5.1.1.
    – Ed Morton
    Nov 3, 2021 at 14:51

2 Answers 2

11

Your calculations are done in local time, and you are affected by the switch over to daylight saving on the 27th of March.

To do the calculation in UTC time instead (Unix timestamps are not in local time), using a recent release of GNU awk, make sure that you pass an extra 1 as a last argument to mktime():

t = mktime(sprintf("%4d %.2d %.2d 00 00 00",
        substr($1,2,4),
        substr($1,6,2),
        substr($1,8,2)), 1);

This is a GNU awk extension available in GNU awk release 4.2.0+.

As an alternative, you could instead avoid using a time around midnight (UTC) as your reference time of day:

t = mktime(sprintf("%4d %.2d %.2d 12 00 00",
        substr($1,2,4),
        substr($1,6,2),
        substr($1,8,2)));

This would make it work in older GNU awk implementations and in any other awk that has the required functions.

Yet another alternative is to make the script run with an altered local time zone:

TZ=UTC awk -f script.awk inputfile

This sets the TZ environment variable to UTC for the execution of the awk script, which alters the time zone used by the mktime() and related functions.

10
  • That is available from 4.2.0 onwards only - lists.gnu.org/archive/html/info-gnu/2017-10/msg00000.html
    – Inian
    Nov 3, 2021 at 8:08
  • @they Thanks.. However i still have two doubts. 1. Which local time was it using for calculations given that server itself is in UK timezone ? 2. Using 12:00:00 instead of 00:00:00 -- will this be making changes to any of the dates not affected by clock change ? Nov 3, 2021 at 8:30
  • @AayushJain 1) It would have used the local time of the system on which it was running. 2) It strikes me as an easy thing to test. Just run your code twice and use diff to compare the results. That would be better than having to trust my word (because I might have overlooked something). However, as long as the code is not run in a +/-12h timezone, it would probably be ok.
    – they
    Nov 3, 2021 at 8:33
  • @AayushJain I'm also investigating a third way to fix this. Check back in a few minutes.
    – they
    Nov 3, 2021 at 8:34
  • 1
    @EdMorton Ah, I see. Yes, I never thought about other data types than decimal integers.
    – they
    Nov 3, 2021 at 17:19
1

Using Raku (formerly known as Perl_6)

raku -pe 's/^ D <( (\d**4)(\d**2)(\d**2) )> \, /{ "$0-$1-$2".Date.earlier(:7days).Str.subst("-", :g); }/;'

Sample Input (blank lines removed):

D20220327,S2927,977,1
D20220328,S2927,977,1
D20220329,S2927,977,1
D20220330,S2927,977,1
D20220331,S2927,977,1
D20220401,S2927,977,1
D20220402,S2927,977,1
D20220403,S2927,977,1
D20220404,S2927,977,1

Sample Output:

D20220320,S2927,977,1
D20220321,S2927,977,1
D20220322,S2927,977,1
D20220323,S2927,977,1
D20220324,S2927,977,1
D20220325,S2927,977,1
D20220326,S2927,977,1
D20220327,S2927,977,1
D20220328,S2927,977,1

Briefly, Raku's linewise (autoprinting) -pe flags are used, in conjunction with the familiar s/// operator. Digits are captured into match variables $0, $1, and $2 with parens, and capture markers <( … )> are employed to drop all other elements of the match.

In the replacement, Raku executes code inside a { … } block. The $0, $1, and $2 captures are stringified with appropriate dashes (-), and this string is recognized as a Date object, upon which the earlier(:7days) method can be called. [Note: some users may find writing earlier(days => 7) to be a more familiar syntax--either form works]. Once the Date object is set back 7 days, its Str-ingified and subst is used to remove the dashes (-) in the return.

https://docs.raku.org/routine/Date
https://docs.raku.org/routine/Dateish
https://raku.org

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.