0

What is the best way to convert local datetime to mysql datetime ?

example :

const date1 = new Date(1658483664000);
console.log(date1);

output :

Fri Jul 22 2022 16:54:24 GMT+0700 (WIB) // Get My Local Datetime

How do I turn it into mysql datetime :

2022-07-22 16:54:24

Thank You

Dennis Liu
  • 2,268
  • 3
  • 21
  • 43
  • Did you try `new Date().toISOString()` which should give you something like `2022-07-22T16:54:24+07:00` – phuzi Jul 22 '22 at 11:39
  • new Date().toISOString() give another timezone. I want my local time (Indonesia). Also I want to convert it to Mysql Datetime – Dennis Liu Jul 22 '22 at 11:40
  • What time is it now where you are and what does the above give you? It's likely the same point in time. – phuzi Jul 22 '22 at 11:41
  • new Date().toISOString() give me : 2022-07-22T11:42:29.803Z. My time now is Friday 22 July 18.43 – Dennis Liu Jul 22 '22 at 11:43
  • Okay so, `2022-07-22T11:42:29.803Z`, `2022-07-22T18:42:29.803+0700` and `Friday 22 July 18.43` all represent the same point in time. These are effectively all the same. – phuzi Jul 22 '22 at 11:44
  • Yes. this one : 2022-07-22T18:42:29.803+0700 is indonesia time, my location – Dennis Liu Jul 22 '22 at 11:45
  • Which is equivalent to the value you get! FYI - It is generally a good idea to save dates & times in databases using UTC not local time. – phuzi Jul 22 '22 at 11:46
  • how do you get my localtime in this format 2022-07-22T18:42:29.803+0700 ? I cannot use UTC time, all my datetime is my local time. And I need to use this localtime for in app purchase – Dennis Liu Jul 22 '22 at 11:47
  • It ___IS___ the same time! – phuzi Jul 22 '22 at 11:48
  • Related question: [How to store a datetime in MySQL with timezone info](https://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info) – Yogi Jul 22 '22 at 11:54

2 Answers2

1
 const date1 = new Date(1658483664000);
 var date =moment(date1 ).format('MM-DD-YYYY HH:mm:ss');

to use this code you need to include moment.js in your project https://momentjs.com

Techi Tamizha
  • 41
  • 3
1

Moment.js is a fine library, but it is heavy and its maintainers consider it a legacy project. See this page for an explanation and a list of alternatives that the moment.js team suggests.

One thing to look into in native JavaScript is the Intl.DateTimeFormat object, which allows you to format a Date in a highly customizable, locale-based format. You can choose locale formats that are close to what you're looking for and manipulate them to get the final desired result. For example,

const date = new Date(Date.now());

const formatOptions = {
  timeZone: 'Asia/Jakarta', 
  dateStyle: 'short', 
  hour12: false, 
  timeStyle: 'medium'
};

console.log(new Intl.DateTimeFormat('en-CA',formatOptions).format(date).split(',').join(''));
// example output: "2022-07-24 04:29:31"

This relies on the fact that Canadians format their dates YYYY-MM-DD, for example. Additionally, the split and join are necessary because the formatter initially gives you "2022-07-24, 04:29:31"

Finally, if you have control over how these are written to the mysql database, you can use the STR_TO_DATE function to convert any date string from a known format into a MySQL DATETIME value.

M-N
  • 621
  • 7
  • 20
  • Must put => year: 'numeric', month: 'numeric', day: 'numeric' and hour: 'numeric', minute: 'numeric', second: 'numeric' to show the time. I see this documentation, https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/DateTimeFormat – Dennis Liu Jul 27 '22 at 04:13