Table of contents
- Introduction
- Date formats
- Time zones
- Unix timestamp
- The Date object
- Calculating the difference between dates
- Formatting the date
- Time zone on the back end
- Storing dates in the database
- Conclusion
- Useful resources
Introduction
Time plays an essential role in our lives. Therefore, as developers we get to work with date and time quite frequently. Computer software is used by people from anywhere in the world and the time in one region can differ from the time in another region. And, generally in the software we build we capture and process the date and time of various data records and events. Therefore, it's important to understand various nuances around working with date and time.
Date formats
Date and time can be represented in many different formats. In Ukraine, the date is usually written in the format "DD-MM-YYYY" (the day is first), while in the United States the common format is "MM-DD-YYYY" (the month is first).
Time zones
Depending on the geographical area or a political unit (e.g., a country), the time zone can differ. For example, in the summer, while in Kyiv the time is 21:00, in New York the time is 14:00 (minus seven hours). Also, it's important to mention that the time zone choice/definition in a particular country/region may be affected by a political decision. A country can choose a UTC offset that differs from that country's actual georgraphical location's UTC offset.
All time zones are defined as offsets to Coordinated Universal Time (UTC), ranging from UTC-12:00 to UTC+14:00. The geographical reference point for UTC is the prime meridian (longitude = 0°), that runs through the Royal Observatory in Greenwich, London, United Kingdom.
Additionally, in various regions of the world people practice Daylight Saving Time (DST) or in other words - summer time. This is the practice of turning the clock forward one hour from standard time during the warmer months in order to get more daylight during the hours we're usually awake. For example, in Ukraine we turn the clock forward one hour on the last Sunday of March at 03:00 and we return back to the standard time (turning the clock backward one hour) in the last Sunday of October at 04:00. The date and time DST starts and ends depends on a country or a political unit (e.g., European Union.)
Also, a political region's time zone offset and/or the date and time when the DST starts and ends could have been changed at some point in history. Therefore, there is a number of databases where the information about the time zone changes is tracked. The most popular one is IANA - Time Zone Database, also known as tz or zoneinfo. Operating systems and other computer software use these databases for time zone processing and conversions.
Unix timestamp
A common format to represent date and time values in computer programming is the Unix timestamp, which is a number of seconds between the Unix epoch (1 January 1970 00:00:00 UTC) and another date (e.g., 1620758838). Positive integers represent the dates after the Unix epoch, while negative integers represent the dates before the Unix epoch.
The Unix timestamp is one of the common formats for storing date and time in operating systems and databases.
Learn more about the Unix timestamp on Wikipedia: Unix time.
The Date object
Fundamentally, a JavaScript date is specified as the number of milliseconds from the 1 January 1970 00:00:00 UTC. This number looks similar to the Unix timestamp. The difference is that the Unix timestamp is the number of seconds since the Unix epoch while in JavaScript it is the number of milliseconds.
In JavaScript we use the Date object to work with date and time:
const date = new Date();
The Date object holds the current date and time in UTC internally. However, by default, when we output the date and time, the Date object converts the output to the local time zone, the time zone of the environment where the JavaScript code is running (e.g., a browser, Node.js, etc.)
When instantiated without parameters, the date object holds the current date and time (new Date()
holds the current date).
We can set the date and time in the Date's constructor or after the instantiation:
let currentDate = new Date();
let someDate = new Date('December 12, 2020 18:30:01');
let someDate = new Date('2021-12-12T18:30:01');
let someDate = new Date(2021, 11, 12, 18, 30, 1); // Month's index is zero-based
let someDate = new Date(1639326601000); // Number of milliseconds from Jan 1, 1970 00:00:00 UTC
When we instantiate the Date class, the specified date is converted to and tracked in UTC internally. However, when we access the date using the basic methods (e.g., toString
, toLocaleString
, getHours
, etc) the date is returned in the local time zone:
const myDate = new Date('11 May 2021 18:30:01 UTC');
console.log(myDate);
// Tue May 11 2021 21:30:01 GMT+0300 (Eastern European Summer Time)
console.log(myDate.getHours());
// 21, not 18 as you might have expected
Here we instantiate the Date class specifying the date in UTC, but when we print the instantiated date object, we get the date in the local time zone, which for me is UTC+03:00 (Eastern European Summer Time). So, the date is stored in UTC internally, but when we access the date using the basic methods, it's converted to the local time zone.
The most popular methods of the Date object are:
Date.now();
// number of milliseconds since the Unix Epoch
new Date().getTime();
// same
Date.UTC(2021, 4, 14, 0, 0, 0);
// number of milliseconds since the Unix epoch until the specified date
const dateBasedOnUTC = new Date(
Date.UTC(2021, 4, 14, 0, 0, 0)
);
const date = new Date();
// Access and modify the date based on local time zone:
date.getFullYear(); // for example, 2021
date.setFullYear(2021);
date.getMonth(); // zero-based month index (0 - 11)
date.setMonth(0);
date.getDate(); // day of the month (1 - 31)
date.setDate(28);
date.getHours(); // 0 - 23
date.setHours(23);
date.getMinutes(); // 0 - 59
date.setMinutes(8);
date.getSeconds(); // 0 - 59
date.setSeconds(59);
// Access and modify the date based on UTC:
date.getUTCFullYear();
date.setUTCFullYear(2021);
date.getUTCMonth();
date.setUTCMonth(11);
date.getUTCDate();
date.setUTCDate(12);
date.getUTCHours();
date.setUTCHours(9);
date.getUTCMinutes();
date.setUTCMinutes(0);
date.getUTCSeconds();
date.setUTCSeconds(0);
// Other useful methods:
date.toISOString();
// Get the date string in simplified extended ISO format (ISO 8601)
date.toLocaleString('uk', { timeZone: 'UTC' });
// Get the date string in the format according to a given locale and various options
date.toLocaleString('en', { month: 'long' });
// Get the name of the month in a given locale (e.g., June, July, etc)
Learn more about the Date object on MDN Web Docs: Date
Calculating the difference between dates
const date1 = new Date(Date.UTC(2021, 4, 11, 0, 0, 0));
const date2 = new Date(Date.UTC(2021, 4, 12, 0, 0, 0));
const diffInMilliseconds = date2 - date1;
const diffInHours = diffInMilliseconds / 1000 / 60 / 60;
console.log(diffInHours); // 24
Here, we create two date objects, 11 May 2021 and 12 May 2021. Then, we subtract 11 May 2021 (date1
) from 12 May 2021 (date2
) and get 24 hours of difference.
When you work with date objects, take into account the time zones these date objects were instantiated with. Check another example:
const date1 = new Date('2021-05-12T00:00:00.000Z'); // ISO 8601 string
const date2 = new Date('12 May 2021 01:00:00 UTC+3');
const diffInMilliseconds = date2 - date1;
const diffInHours = diffInMilliseconds / 1000 / 60 / 60;
console.log(diffInHours); // 1 or -2?
Here, the answer is -2, because the time zone of date1
is UTC while the time zone of date2
is UTC+3. Let's see what happens in more detail:
- We set
date1
to 12 May 2021 00:00:00 UTC using the ISO 8601 format. We specify the UTC time zone using the character Z. We could specify the time zone using an offset as well, like this: 2021-05-12T00:00:00.000+00:00 (+00:00 is UTC). - We set
date2
to 12 May 2021 01:00:00 using another date and time format with the time zone offset UTC+3 (or UTC+03:00). - Then we subtract date1 from date2 (
date2 - date1
). When we use a mathematical operation on an object, JavaScript converts the object to a number using the object's valueOf() method if it's present. In the case of the Date object, thevalueOf
method returns the date and time as the number of milliseconds since the Unix epoch, which is a UTC-based date and time representation. So,date1
has been set in UTC, whiledate2
has been converted to UTC from UTC+3. So, actually, we subtract 12 May 2021 01:00:00 UTC (date1
) from 11 May 2021 22:00:00 UTC (date2
) and that's how we get -2 for the result.
Formatting the date
One way to format a date is to use the getter functions like getFullYear, getMonth, getDate, etc. For example, to create the date and time string in local time in the format 'YYYY-MM-DD hh:mm:ss' we can do the following:
const date = new Date();
const year = date.getFullYear();
const month = date.getMonth(); // getMonth returns a zero-based index of the month: 0-11
const day = date.getDate(); // 0 - 31
const hours = date.getHours(); // 0 - 23
const minutes = date.getMinutes(); // 0 - 59
const seconds = date.getSeconds(); // 0 - 59
const addZero = (num) => `${num}`.padStart(2, '0');
const formatted =
year +
'-' +
addZero(month + 1) +
'-' +
addZero(day) +
' ' +
addZero(hours) +
':' +
addZero(minutes) +
':' +
addZero(seconds);
In order to get the date and time values according to UTC, we can use the UTC-based getter functions like getUTCFullYear, getUTCMonth, getUTCDate, etc.
Furthermore, we can get the date and time string from the Date object formatted according to a given locale and various options using the Date's toLocaleString
method:
new Date('2021-05-16').toLocaleString('uk');
// 16.05.2021, 03:00:00
new Date('2021-05-16').toLocaleString('en', {
month: 'long',
});
// May
new Date('2021-05-16').toLocaleString('en', {
month: 'long',
day: 'numeric',
year: 'numeric',
});
// May 16, 2021
Please note that if you don't specify the time zone in the options, toLocaleString
will output the date in the local time zone:
new Date('2021-05-16T00:00:00').toLocaleString('en', {
timeZoneName: 'short', // include the time zone name
});
// "5/16/2021, 12:00:00 AM GMT+3" (GMT+3 is my local time zone)
new Date('2021-05-16T00:00:00').toLocaleString('en', {
timeZoneName: 'short',
timeZone: 'Europe/Amsterdam',
});
// 5/15/2021, 11:00:00 PM GMT+2
First, we instantiate the Date object with the local time zone and get the localized date and time string. When the Date is instatiated the date and time value is converted internally from the specified time zone (or the local time zone if the time zone is not specified) to UTC.
Then, we instantiate another Date object with the local time zone. This time we set the timeZone
option to "Europe/Amsterdam" (according to IANA Time Zone Database). Therefore, we get the localized date and time string based on the "Europe/Amsterdam" time zone. In more detail: my current time zone is UTC+3, so the Date object is instantiated with the date and time 2021-05-16 00:00:00 UTC+3 which is converted to UTC internally and becomes 2021-05-15 21:00:00. Then, the toLocaleString
method converts this date from UTC to "Europe/Amsterdam" and the result is 5/15/2021, 11:00:00 PM GMT+2. The resulting offset is +2, because it's the Daylight Saving Time offset of the "Europe/Amsterdam" time zone.
Time zone on the back end
Usually, computer software (e.g., Node.js, databases, etc) by default uses the system date and time configuration. Therefore, make sure that all the servers that belong to your app use the same time zone. UTC is commonly used.
In Linux you can check the time zone using one of the following commands:
date
date +"%Z %z"
timedatectl
To change the system time zone you can use the following commands:
# Check available time zone names
timedatectl list-timezones
# Set the time zone
timedatectl set-timezone Europe/Amsterdam
To check the time zone of the Node.js process:
node -e "console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)"
In Node.js you can set the time zone using the TZ
environment variable:
TZ=UTC node -e "console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)"
# UTC
If the TZ
variable is not set, Node.js will attempt to use the operating system's time zone.
Storing dates in the database
Usually, web applications are available from anywhere around the world. This means, that if we store dates in the database, we need to make sure that we store all dates in a single (consistent) time zone or at least track the time zone of the dates we store. Various databases have their specifics as to how they treat date and time values. So, in this section I'd like to briefly explain how to store the date and time values in different databases and mention some of those specifics.
Usually, software (especially web apps) serves users from all over the world. Each user's local time depends on their location. For example, for the user from Kyiv the local time in the summer is 17:00 and at the same time for the user in Amsterdam the local time is 16:00.
Basically, we should convert and store the dates in a unified time zone; usually developers store the dates in UTC. So, before storing a date we should convert it from local time to UTC. For example, a user from Amsterdam creates a reminder in a calendar app. The user selects the date and time of the reminder in local time ("Europe/Amsterdam" time zone - UTC+01:00 standard time and UTC+02:00 DST). Let's say the user sets the reminder date to 12 May 2021 17:00 using a calendar widget. Now, before we store this date in the database, we should convert it to UTC, and the date becomes: 12 May 2021 15:00 in UTC, because Daylight Saving Time in Amsterdam (UTC+02:00) became active on 28 March 2021 at 02:00:00. Luckily, we usually don't have to deal with the time zone conversion logic, because it's usually built into the programming language and software (e.g., a database) we use. For example, in JavaScript we can get the current date and time in UTC using the Date object:
new Date().getTime();
// number of milliseconds from Unix epoch
new Date().toISOString();
// ISO 8601 date string in UTC
MySQL
In MySQL the dates are usually stored using the DATETIME
and TIMESTAMP
column types.
MySQL TIMESTAMP
MySQL converts TIMESTAMP
values from the current time zone (e.g., the global time zone or the client session time zone) to UTC for storage, and back from UTC to the current time zone for retrieval. MySQL doesn't do this with the other column types, like DATETIME
. So, TIMESTAMP
values are converted to UTC for storage, but when they are retrieved, they are converted to the current time zone (SELECT @@SESSION.time_zone;
). Please check this example:
CREATE TABLE timestamp_test (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
created_at TIMESTAMP NOT NULL
);
-- Set the current time zone to UTC+3
SET time_zone = '+03:00';
SELECT @@SESSION.time_zone;
-- +03:00
INSERT INTO timestamp_test (created_at) VALUES ('2021-05-12 21:00:00');
SELECT * FROM timestamp_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 21:00:00 |
+----+---------------------+
-- Set the current time zone to UTC+4
SET time_zone = '+04:00';
SELECT * FROM timestamp_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 22:00:00 |
+----+---------------------+
As of MySQL 8.0.19 we can add the time zone offset when inserting TIMESTAMP
and DATETIME
values into a table:
-- 1.
SET time_zone = '+02:00';
-- 2.
INSERT INTO timestamp_test (created_at) VALUES ('2021-05-12 21:00:00-01:00');
-- 3.
SELECT * FROM timestamp_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 20:00:00 |
| 2 | 2021-05-13 00:00:00 |
+----+---------------------+
-- 4.
SET time_zone = '+03:00';
SELECT * FROM timestamp_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 21:00:00 |
| 2 | 2021-05-13 01:00:00 |
+----+---------------------+
Let's see what happens in this example:
- We set the current time zone to UTC+2
- We insert a date value with time zone offset UTC-1: 2021-05-12 21:00:00-01:00. MySQL converts this date and time value from UTC-1 to UTC for storage: 2021-05-12 22:00:00.
- We select the rows from our table and MySQL converts the
TIMESTAMP
values from UTC to the current time zone (UTC+2) upon retrieval: 2021-05-13 00:00:00 - Then, we set the time zone to UTC+3 and select the rows again, and because MySQL converts the
TIMESTAMP
values from UTC to the current time zone upon retrieval, we get the date we've inserted earlier in UTC+3 this time: 2021-05-13 01:00:00
The TIMESTAMP
type has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.
MySQL DATETIME
MySQL DATETIME
is stored and retrieved as is:
CREATE TABLE datetime_test (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME NOT NULL
);
-- Set the current time zone to UTC+3
SET time_zone = '+03:00';
INSERT INTO datetime_test (created_at) VALUES ('2021-05-12 21:00:00');
SELECT * FROM datetime_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 21:00:00 |
+----+---------------------+
-- Set the current time zone to UTC+4
SET time_zone = '+04:00';
SELECT * FROM datetime_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2021-05-12 21:00:00 |
+----+---------------------+
MySQL does not convert DATETIME
values from the current time zone to UTC and vice versa.
However, something interesting happens when we insert a DATETIME
value with a time zone offset:
-- Set the current time zone to UTC+3
SET time_zone = '+03:00';
-- Insert a date with the time zone offset UTC+0
INSERT INTO datetime_test (created_at) VALUES ('2021-05-12 21:00:00+00:00');
SELECT * FROM datetime_test ORDER BY id ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| ... |
| 2 | 2021-05-13 00:00:00 |
+----+---------------------+
In the example above we can see that when we insert a DATETIME
value with a time zone offset, MySQL converts this date value to the current time zone for storage: 2021-05-12 21:00:00+00:00 + current time zone offset (+03:00) = 2021-05-13 00:00:00
The DATETIME
type has a range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
To store a date and time value I'd convert the local date and time value to UTC in the application code and store this value in the DATETIME column. Basically, I'd handle the time zone conversion on the application side and store all dates in UTC. I'd use DATETIME instead of TIMESTAMP, because TIMESTAMP is actually a 32 bit integer and doesn't allow to store dates beyond 2038-01-19 03:14:07 UTC, while DATETIME allows us to store dates up to 9999-12-31 23:59:59.999999.
PostgreSQL
In PostgreSQL we usually store date and time values using the column types timestamp without time zone
(or simply timestamp
) or timestamp with time zone
(or simply timestamptz
). Both types have a range of 4713 BC to 294276 AD.
PostgreSQL timestamp without time zone
The timestamp without time zone
values are stored and retrieved as is:
CREATE TABLE without_time_zone(
id SERIAL PRIMARY KEY,
created_at TIMESTAMP NOT NULL
);
INSERT INTO without_time_zone (created_at) VALUES ('2021-05-12 09:00:00');
SELECT * FROM without_time_zone ORDER BY id ASC;
-- 1 "2021-05-12 09:00:00"
INSERT INTO without_time_zone (created_at) VALUES ('2021-05-12 09:00:00 +01:00');
SELECT * FROM without_time_zone ORDER BY id ASC;
-- 1 "2021-05-12 09:00:00"
-- 2 "2021-05-12 09:00:00"
In this example, we create a timestamp without time zone
column called created_at. And, we can clearly see that the date and time value is stored and retrieved from this column as is. If we specify the time zone, the time zone is ignored.
PostgreSQL timestamp with time zone
The timestamp with time zone
(timestamptz
) values are converted from the specified time zone to UTC. If you don't specify the time zone when inserting a timestamptz
value, PostgreSQL will assume that this date value is based on the current time zone (SHOW time zone;
).
-- 1.
CREATE TABLE with_time_zone(
id SERIAL PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
-- 2.
-- Set current time zone for the session to UTC+3
SET time zone 3;
-- 3.
SHOW time zone;
-- <+03>-03
-- 4.
INSERT INTO with_time_zone (created_at) VALUES ('2021-05-12 09:00:00 +01:00');
-- 5.
SELECT * FROM with_time_zone ORDER BY id ASC;
-- 1 "2021-05-12 11:00:00+03"
-- 6.
INSERT INTO with_time_zone (created_at) VALUES ('2021-05-12 09:00:00');
-- 7.
SELECT * FROM with_time_zone ORDER BY id ASC;
-- 1 "2021-05-12 11:00:00+03"
-- 2 "2021-05-12 09:00:00+03"
What happens in this example is the following:
- We create a table with a column called created_at which has type
timestamp with time zone
. - We set the current time zone for the session to UTC+3. Please note, that in PostgreSQL we can specify the current time zone using an integer offset or the time zone name (e.g., America/New_York). To get a list of supported time zone names query the pg_timezone_names table:
SELECT * FROM pg_timezone_names
- We ensure that the time zone offset has been set properly.
- We insert a row with the created_at value of 2021-05-12 09:00:00 +01:00 into the new table. Because created_at column has type
timestamp with time zone
, PostgreSQL converts the date value from the specified time zone (+01:00 in our case) to UTC for storage. So, the value that has been actually stored is "2021-05-12 08:00:00". - We select the inserted row from the table and the date value is converted from UTC to the current time zone offset, which is UTC+3.
- We insert another row into the table, but this time without specifying the time zone offset. If we don't include a time zone offset, PostgreSQL assumes that the date value we provide is based on the current time zone, which in our case is UTC+3. So, before storing this date value PostgreSQL will convert it from the current time zone (UTC+3 in our case) to UTC, and the following date value will be stored: 2021-05-12 06:00:00 UTC
- Finally we select all rows from our table again and see that the date value we've just inserted is displayed in the current time zone: 2021-05-12 09:00:00+03
To store a date and time value in PostgreSQL I'd use the timestamp with time zone (timestamptz)
type. Basically, I'd choose timestamptz instead of timestamp, because of the reasons mentioned in the following article from the PostgreSQL Wiki: Don't Do This - Date/Time storage.
DynamoDB
You can use the string and number data types to store dates and timestamps. We can use the string data type to store date and time values in ISO 8601 format (e.g., 2021-05-12, 2021-05-12T21:00:59.123Z). And we can use the number data type to store date and time values as UNIX timestamps (Epoch time) - a number of seconds since 1 January 1970 00:00:00 UTC. This will allow us to sort and query items by date and time.
MongoDB
In MongoDB, internally, dates are stored as 64-bit signed integers representing a number of milliseconds since the Unix epoch (1 January 1970 00:00:00 UTC). Negative values represent the dates before the Unix epoch.
To insert a date in the Mongo shell, we can use the Date object:
// Specifying the current date and time value when inserting an item:
db.books.insertOne({ title: 'Book 1', createdAt: new Date() })
db.books.find()
/*
{
"_id" : ObjectId("60a5481cdff09849800652de"),
"title" : "Book 1",
"createdAt" : ISODate("2021-05-19T17:17:16.634Z")
}
You can pass an ISO 8601 string or a number of milliseconds since the Unix epoch into the Date constructor:
new Date('2021-05-19');
new Date('2021-05-19T00:00:00Z');
new Date(1621382400000);
If you specify a time zone offset, the date will be converted to UTC:
new Date('2021-05-19T00:00:00+02:00');
// becomes 2021-05-18T22:00:00Z
Conclusion
Take care when working with date and time. Always check if the dates you store and work with are based on the time zone you expect. Ensure that the environments you use for running different parts of your app (e.g., a web server, a database, etc.) have the correct and consistent time zone configuration.
Useful Resources
- Time zone - Wikipedia
- Date - MDN Web Docs
- The DATE, DATETIME, and TIMESTAMP Types - MySQL Manual
- MySQL Server Time Zone Support - MySQL Manual
- Date/Time Types - PostgreSQL Documentation
- Don't Do This - Date/Time storage
- Data Types - Amazon DynamoDB Developer Guide
- Data Types in the mongo Shell - MongoDB Manual
- BSON Types - Date - MongoDB Manual
- List of UTC time offsets
- Daylight saving time by country
- tz database - Wikipedia
Top comments (6)
Thanks for this amazing article. Just one small point. Be careful with date format.
The pattern YYYY-MM-dd is highly not recommended...
Here are some supported strings:
MM-dd-yyyy
yyyy/MM/dd
MM/dd/yyyy
MMMM dd, yyyy
MMM dd, yyyy
And the last one : YYYY-MM-dd
BUT : for some reason, Safari doesn't support it...
Safari:
Date.parse("2021-05-16 11:57:00")
NaN
😤
Good point! Thank you. We should use the letter "T" as a delimiter between the date and time values (T
An long lurking thought cleared by you.
amazing article ☕
Thank you
Very details and easy to understand about date + time + timezone knowledge. Thank you for your hard work
Thank you