Understanding Timestamps and Timezones-Wahab's Blog

Dates

Dates are generally formed of 3 triplets, namely, {Day, Month, Year}. This already can create a lot of confusion around the world as they are ordered differently. For example, the date {16, 02, 2014} can be written as:

Additionally, there are many ways to seperate these triplets. Some use / whereas others use - and .. All this variation was sandardized using the ISO-8601.

Timestamps

This standard allows us the view the same time regardless of where we live. The most commonly used notation is:

YYYY-MM-DDThh:mm:ss.sssZ

Where:

YYYY-MM-DD is the date
T is the seperator between the date and time
hh:mm:ss:sss is the time, with hours minuts, seconds and milliseconds
Z indicates the time is in the Coordinated Universal Time (UTC)

It it important that you understand the system you are working with and do not omit the Z from the timestamp. The absence of it means that timezone is not specified and we cannot confirm which timezone is being used.

THIS IS DANGEROUS AND CAN LEAD TO ERRORS

! Timezones !

Lets talk more about timezones. As alluded to before, timezones are optional as per the ISO-8601 standard. However, developers to consider them manditory since otherwise they can lead to many issues.

So let us revisit the timestamp format:

YYYY-MM-DDThh:mm:ss.sssZ

The Z under the hood is really just the offset for specific timezones +/- HH:MM.

So an offset of 0 implies you are in UTC timezone.
An offset of -05:00 means you are 5 hours behind the UTC timezone. This would land you in New York
An offset of +05:00 means you are 5 hours ahead of the UTC timezone. This would land you in Uzbekistan.

The very first second of 1970 in UTC would in New York and Uzbekistan be:

1970-01-01T19:00:00.000-05:00   (New York: 7:00PM Pervious day)
1970-01-01T05:00:00.000+05:00   (Uzbekistan: 5:00AM)

The very first second in New York and Uzbekistan in UTC time would be:

1970-01-01T00:00:00.000-05:00   (UTC: 5:00AM)
1970-01-01T00:00:00.000+05:00   (UTC: 7:00PM, previous day)

One surprising fact is that timezones are still not set in stone. You can see here as recent as this year (2023). Timezones have been getting updated in several countries.

Introduction of Day Light Savings time :(

Daylight savings time was introduced in 1925 with the basic idea to move ahead one hour during the summer when the day is longer and sunsets later. Them move back to the correct time in autum when the days are shorter. The whole purpose of this move to allow people make more use of daylight. However, this adds another complexity to understanding time.

The complexity is that dates for these day light offsets are set and need to be accounted for during conversions. So here in Toronto, we can find the DST times here.

When we are in daylight savings time, we are already one hour ahead. For instance, in New York during DST, we are already 1 hour ahead, so the offset -04:00 instead of -05:00. Generally, when daylight savings occurs you timezone changes (for me atleast) from EST (Eastern Standard Time) to EDT (Eastern Daylight Savings Time)

Annoying Vitamen D optimization from Benjamin Franklin :(

Using PSQL to convert between timezones

If you can’t trust yourself with understanding all these complexities. I will encourage use you use your DB for the conversion or some CLI tool. To convert between timezones on postgres sql we can use the following query:

SELECT '2023-03-12 01:30:00'::timestamp AT TIME ZONE 'EDT' AT TIME ZONE 'UTC';

Which will return 5:30 same date and the following will return 21:30 previous date:

SELECT '2023-03-12 01:30:00'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'EDT';

If you db config is setup correctly you can also rely on you db to convert between EDT/EST. But be careful that it is setup correctly.

SELECT '2023-03-12 01:30:00'::timestamp AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC';

Unix Epoch Timestamp

The number of milliseconds since midnight of Thursday January 1st, 1970 UTC. The reason for this date is because the unix system was build during the 60s and early 70s and thus unix timestamp conventions stuck along.

1634870400 -> Fri 22 Oct 2021 02:40:00 UTC

A quick way to convert Unix Epoch Timestamps to readable time is to use the date command date -u -r 1634870400.

-u is used to specify the output date should be in UTC
-r options is used to specify the timestamp

Dealing with date and time on Postgres

Get Current DateTime in UTC

select now(); -- date and time
select current_date; -- date
select current_time; -- time

Rows between two timestamps

select count(*)
from table
where time between '2018-01-01' and '2018-01-31'

Rows created between an interval

select count(1)
from events
where time > now() - '1 week'::interval;

OR:

select count(1)
from events
where time between (now() - '1 week'::interval) and (now() - '2 weeks'::interval);

Extract part of a timestamp

select date_part('minute', now()); -- or hour, day, month

-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
select date_part('dow', now());

-- returns a string like monday, tuesday, etc
select to_char(now(), 'day');

Converting to unix timestamp

select date_part('epoch', now());

Understanding Timestamps and Timezones

Contents