Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simple time called now. What you should rather think about is a time comprised of here and now.
The Earth rotates around its own axis. When it’s solar noon (the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.
To make communication easier, at the end of the 19th century, the Earth was divided into 24 hour-wide time zones. All places within one time zone have the same time. It was decided that the “0” time zone would be London’s time zone. The “0” time zone is nowadays called UTC (Coordinated Universal Time) or, for historic reasons, GMT (Greenwich Mean Time). In general, the time zones’ boundaries follow countries’ boundaries though some large countries are located in multiple time zones. For example, USA is located in 7 different time zones, Canada in 6, but China is in one time zone. There are time zones with offsets of 30 minutes or even 45 minutes (Nepal). To make things even more complicated, many countries also have daylight saving time: they turn the clock back or forward one hour on specific days to account for seasonal changes in daylight.
If your computer system is international, it has to be ready to handle users from different time zones. The users want to see the time of system events expressed in their local time zone:
- In an online course, you want to see the assignment deadline in your own time zone. You don’t want to submit your assignment and find out that the deadline has passed.
- In an auction system you want to know at exactly what time the auction ends. You don’t want to try bidding and find out that the auction ended three hours earlier.
- You want to know the time when a promotion ends. Ideally, if the promotion ends on a specific day, you want the promotion to run until the end of the day in UTC-11 time zone.
All databases have types which handle both date and time. The types are called timestamp or datetime, or something similar. They usually come in two flavors: one without time zone and the other with time zone.
Timestamp without time zone is generally a fancy string value. It does not interpret the time zone. If you enter the value “2015-01-28 13:00:00,” the same value will be shown to all users.
Timestamp with time zone is aware of the time zone. When a user, a client or an application connects to the database, the database converts the value to the user’s (connection’s) time zone.
If your system works in multiple time zones, it’s best to store all your timestamps in UTC and then interpret it to an appropriate time zone.
MySQL has two types which allow you to store a date with time:
- datetime – date and time without time zone
- timestamp – MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
PostgreSQL has two types for storing a date with time:
- timestamp without time zone (timestamp) – date and time without time zone information
- timestamp with time zone – as the name suggests, it stores the time zone information
The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.)
SQL Server has four types for storing date and time:
- date – stores just the date
- datetime2 – date and time of day, with optional fractional seconds, no time zone offset
- datetimeoffset – a date and time of day with time zone awareness
- smalldatetime – a date and time of day, with seconds always zero (:00), without fractional seconds; no time zone offset
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
Oracle has more types for storing date and time:
- date – contains date and time in second precision, no time zone information
- timestamp – date and time with fractional seconds precision, no time zone information
- timestamp with time zone – the same as timestamp, but with time zone information
- timestamp with local time zone – the same as timestamp. The data is converted to database’s time zone but upon retrieval it is converted to the local session’s time zone
SQLite does not have types for dates and times. You can use the built-in Date And Time Functions to store dates and times as TEXT, REAL, or INTEGER values:
- TEXT as strings in format “YYYY-MM-DD HH:MM:SS.SSS”
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar (in simple English: the Gregorian calendar extended backwards)
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
HSQLDB has the following types for handling date and time:
- DATE – represents a date with YEAR, MONTH and DAY fields.
- TIMESTAMP – represents date and time with no time zone information
- TIMESTAMP WITH TIME ZONE – represents date and time with time zone information
TIMESTAMP can have fractional second parts. For example, TIMESTAMP(6) has six fractional digits for the second field. If fractional second precision is not specified, it defaults to 6 for TIMESTAMP.
When you start to think about how to store date and time values in your database, always take into account if the users come from one time zone or from multiple time zones. If they are in one time zone, you can store your dates in this time zone. If they are in multiple time zones, my recommendation is to store the date and time in UTC and convert them to the right time zone when presenting to the user.