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).
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.
# 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)
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.