MySql Timestamp – Right way to store datetime in tables

I struggled with the issue of storing date and time in database for a long time. Due to different time zones in the world, it is very hard to get a universal time which can work for all places. In this problem, Mysql timestamp is up for rescue.

Suppose your server is in USA. So, the time zone, which your database is supporting, could be UTC-05:00 or simply UTC.

Now, a user from Australia is posting a date and time for sales call on your website. Let say 12th December 2020 at 08:00 PM. You need to call that user at her provided time only. This datetime will be in Australian time zone. But your server will store it as it is and sales person will call her at USA 08:00 PM.

Australian User provided – 12th December 2020, 08:00 PM

Australian time when USA sales person call, will be – 13th December 2020, 01:00 PM

To deal with this issue, Mysql timestamp is used. Timestamp is the total seconds or milli seconds passed from 01st January 1970. That’s why this value is same for all the time zones and locations at a particular point, no matter what the time is.

So, timestamp of Australia on 12th December 2020, 08:00 PM = timestamp of USA on 12th December 2020, 03:00 AM

That’s why the USA sales person needs to call at 03:00 AM to the Australian user.

Solving issue with Mysql Timestamp

Step 1: Convert user provided date time into timestamp using JavaScript.

For getting current time of the user –

var timestamp = Date.now();

Timestamp of any user provided datetime –

var d = new Date("2015-03-25 08:00:00");
var timestamp = d.getTime();

Note: Javascript returns timestamp in milli seconds. But Mysql expects in seconds. So, we need to divide it by 1000 before inserting in Mysql.

Step 2: Send this value to the server

Step 3: Store timestamp in MySql table by converting in datetime format.

FROM_UNIXTIME(javascript timestamp / 1000)

Step 4: Convert back to timestamp when fetching from table.

UNIX_TIMESTAMP(`timestamp column of mysql`)

This command will return the total seconds. Now we can send these seconds to browsers for converting them to appropriate date time in its time zone.

Step 5: Browsers converting timestamp value to datetime according to their time zone

var d = new Date(timestamp in seconds * 1000);

Remember, javascript needs timestamp in milli seconds that’s why we multiplied by 1000.

You may also like –