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
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();
Step 2: Send this value to the server
Step 3: Store timestamp in MySql table by converting in datetime format.
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);
You may also like –