Date- and time-related computations are hard – we all know this. Sometimes the problem is that there are many edge cases, sometimes the issue is conceptually hard, and sometimes both. Recently, I had an instance of a problem which was conceptually hard (at least for my little brain).
Here is the thing. I was writing a script (and I made the poor decision to use Bash. “This is simple, let’s make it a shell script” should be definitely added to the list of Famous Last Words) which should query the database and extract every entry “up to yesterday, inclusive” (of course, there was a timestamp column somewhere in the database), and then output a csv file with the data, named after “yesterday” (so, like e.g. data-2020-03-21.csv).
Easy enough, right? Not so fast. The server’s clock was (of course) set to UTC, and the notion of “yesterday” which should be used was in a (very) different timezone, say America/New_York.
So, I needed this: first of all, a UTC timestamp of, say, 23:59:59 yesterday in NY, for the purpose of database extraction, and then the date (so only the y-m-d part) of yesterday – this time in NY timezone, not UTC, for the purpose of creating a user-friendly string (assuming the user lives in NY).
It turns out that the GNU date utility can actually handle this pretty easily. Let’s start with the UTC timestamp. If I say
date -d 'TZ="America/New_York" yesterday 23:59:59' -Iseconds -u
I get exactly what I needed. The -u flag gives the timestamp in UTC, -Iseconds yields the ISO-8601 format with second precision, -d tells date to use something other than “now”, and that “something other” is the main part of the trick.
The “time of day” part (which comes last in the above example) gives today, 23:59:59. The “yesterday” part is the so-called “relative item” and means backing up by 24 hours. Last but not least, the TZ part in the date string means that the given time should be interpreted in NY’s local time (note: it looks like setting an environment variable, but it is actuall a parameter parsed by date). This way, we get a UTC timestamp corresponding to yesterday, a second before midnight, in NY.
What I needed next is the human-friendly string describing the date part of the same timestamp – but this time, in NY’s time, not UTC. I cannot say just date -d yesterday -Idate, because I am not sure the date is the same in UTC and in NY right now - for example, I am in Poland (UTC+1), NY is UTC–5, so at midnight UTC, it is 2021-11-20 here and 2021-11-19 in NY. So, what I need to know what date it was 24 hours ago in NY. Simple now:
TZ=America/New_York date -d yesterday -Idate
In other words, while the TZ="..." part in the date string means “interpret the given date/time in the given time zone”, the TZ environment variable means “display the given date/time using the given time zone. Incidentally, this means that it is very easy to convert between timezones with GNU date – to convert 2021-11-13 6:00 in NY time to Warsaw time, I just need to say
TZ=Europe/Warsaw date -d 'TZ="America/New_York" 2021-11-13 6:00' -Iminutes
And date uses a pretty comprehensive time zone information database, so it even knows about the rules for DST (and by the way, DST is started and stopped at different dates in Poland and the US, so it’s a total mess!).