Time zone is a distant relationship
We can freely store date, time, and timestamp data but we need to aware its timezone.
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 UTC + 0.
Now I’m in Thailand and the timezone is ICT which stands for Indochina Time. It is UTC + 7 means it is after UTC 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.
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.
Epoch time always in GMT timezone or UTC time.
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
1
2
3
4
5
6
# 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"
Result of the Bash commands
Running this command below to convert time in different timezones. The value of timezone can be found in the path /usr/share/zoneinfo.
1
TZ=":timezone" date
List of available timezone in Bash
Python
The library datetime can do this job.
1
2
3
4
5
6
7
8
9
10
11
12
13
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")
Result of the Python script
When it comes to convert timezones, the library pytz is great at it.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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))
Result of pytz
Hope this is useful for your works.