Shift of dates not working as expected

Question

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
}

Answer 1

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.

Answer 2

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