Code Self Study Forum

How to get a Javascript Date formatted in Local Time to select from a MySQL timestamp

Javascript has a troublesome date API.

Most of the prebuilt functions like .getMonth() work only for ISO (International Standard Time) Dates. Making local time zones a problem.

My MySQL table stores the time stamp in the U.S. Pacific Coast Timezone. So if I want to pick a date from there I have to reformat the Javascript date in the local time zone.

Here is how!

We will use the toLocaleString function with options.

First define the options
let dateOptions = {timeZone: "America/Los_Angeles", year: 'numeric', month: '2-digit', day: '2-digit' };

Then create the date
var dateToCheck=new Date().toLocaleString("en-US", dateOptions).slice(0, 10);

Then reformat it to YYYY-MM-DD that MySQL needs when you use DATE(timestamp) in MySQL.

var month=dateToCheck.substring(0,2);
var day=dateToCheck.substring(3,5);
var year=dateToCheck.substring(6,10);
var newDateToCheck=year+"-"+month+"-"+day;

Now we can run a MySQL query like
SELECT data from dataTable where DATE(timestamp) = 'newDateToCheck';

I hope this helps someone else out there.

Post here with any questions.

1 Like