Time is one of big problems for us programmers.

Not to mention about our work-life balance or something along those lines even it’s true. This blog will describe how can we manage date and time data in our databases e.g. flight tables, user transactions, etc.

Programmatic problems about the time

We can freely store date, time, and timestamp data but we need to aware its timezone. It can affect our dashboards and other dependent tasks if we overlook this.

Another is formatting. Some database brands support date and time in different format to others. We also need to check this but it will be later blog.

Coordinated Universal Time

Coordinated Universal Time or UTC is a time standard. This is defined at 0° latitude.

Introducing GMT stands for Greenwich Mean Time. This is a timezone. Greenwich is a city in London England and it locates in latitude 0°. Therefore, GMT has same value as UTC and we also call it UCT + 0 (ref: GMT versus UTC (timeanddate.com))

Now I’m in Thailand and the timezone is ICT which stands for Indochina Time. It is UTC + 7 means it is after UCT by 7 hours.

Not only ICT timezone that is UTC + 7 but there still is CXT or Christmas Island Time of a place around Australia. All timezones can be found at Time Zone Abbreviations – Worldwide List (timeanddate.com).

Epoch time

We now are talking about Epoch time. This is a long number indicating seconds since the new year of 1970 in UTC time (1970-01-01 00:00:00 UTC). It’s also known as “Unix time”, “Unix epoch”, “Unix timestamp”, and “POSIX time”.

Epoch time refers seconds yet many system can support it in milliseconds, microseconds, or even nanoseconds. Normally 3600 is 1 hour after reference time or 1970-01-01 01:00:00 UTC.

If we have to deal with Epoch time, an interesting tool is Epoch Converter – Unix Timestamp Converter. This website helps us convert the Epoch time into our local timezone as well.

A time to code

We are able to work with timezones using programs like these.

Bash script

# generate epoch time of current time
date +%s
# convert epoch time to UTC date time
date -d @1609459200
# convert epoch time to UTC date time in format YYYY-mm-dd HH:MM:SS 
date -d @1609459200 +"%Y-%m-%d %H:%M:%S"

Running this command below to convert time in different timezones. The value of timezone can be found in the path /usr/share/zoneinfo (ref: How can I have `date` output the time from a different timezone? – Unix & Linux Stack Exchange)

TZ=":timezone" date

Python

The library datetime can do this job.

from datetime import datetime
# print current timestamp
print(datetime.now())
# print epoch time of current timestamp
print(datetime.now().timestamp())
# convert epoch time to LOCAL date time
print(datetime.fromtimestamp(1609459200))
# convert epoch time to LOCAL date time in format YYYY-mm-dd HH:MM:SS 
datetime.fromtimestamp(1609459200).strftime("%Y-%m-%d %H:%M:%S")

When it comes to convert timezones, the library pytz is great at it.

import pytz
from datetime import datetime

# convert current time to specific timezone: Asia/Bangkok
target_timezone = "Asia/Bangkok"
datetime.now().astimezone(pytz.timezone(target_timezone))

# convert current time to specific timezone: Australia/Melbourne
target_timezone = "Australia/Melbourne"
datetime.now().astimezone(pytz.timezone(target_timezone))

# convert time across timezones
source_timezone = "Asia/Bangkok"
target_timezone = "Australia/Melbourne"
quest_datetime = datetime(2021, 3, 1, 0, 0, 0)

pytz.timezone(source_timezone) \
    .localize(quest_datetime) \
    .astimezone(pytz.timezone(target_timezone)) 

Hope this is useful for your works.