How to convert a Timestamp into MySQL DateTime in JavaScript?

Question

I have a timestamp format which is just like below which is an int.

1631514003973

I am passing this value to an API which is built with Node.JS. How can I convert this into Mysql DateTime format?

I checked this answer but it is about getting the current date and not converting a timestamp. I am coming from a Java background so this is bit confusing for me.

Send the timestamp to MySQL as-is and convert in a query. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ca9b94a30156bcf7c5cf944931283976 — Akina, Sep 13 '21 at 06:35
Does this answer your question? [Function to convert timestamp to human date in javascript](https://stackoverflow.com/questions/19485353/function-to-convert-timestamp-to-human-date-in-javascript) — Rajeev Singh, Sep 13 '21 at 07:23

score 4 · Accepted Answer · answered Sep 13 '21 at 06:54

4

You can pass the Date object of JavaScript directly to MySQL. And MySQL will automatically generate the DateTime format from that Date object.

const date = new Date(1631514003973);

answered Sep 13 '21 at 06:54

Nimna Perera

1,015
10
16

score 1 · Answer 2 · answered Sep 13 '21 at 07:03

1

I assume that integer represents number in milliseconds since the Epoch time. If that is the case try this code:

const numberOfMs = 1631514003973; 
const epochDate = new Date(1970,1,1);
const myDate = new Date(epochDate .getTime() + numberOfMs);

answered Sep 13 '21 at 07:03

Dušan

269
1
11

Rajeev Singh · Answer 3 · 2021-09-13T08:30:30.180

we need to create a new function using JavaScript.

<script>
        var timestamp = 1607110465663
        var date = new Date(timestamp);

       console.log("Date: "+date.getDate()+
          "/"+(date.getMonth()+1)+
          "/"+date.getFullYear()+
          " "+date.getHours()+
          ":"+date.getMinutes()+
          ":"+date.getSeconds());
    </script>
    
    Output:
    Date: 4/12/2020 19:34:25

If you want only date (MM/DD/YYYY), you should fallow this:

var timestamp=1370001284;
var todate=new Date(timestamp).getDate();
var tomonth=new Date(timestamp).getMonth()+1;
var toyear=new Date(timestamp).getFullYear();
var original_date=tomonth+'/'+todate+'/'+toyear;
console.log(original_date);

score 0 · Answer 4 · answered Sep 13 '21 at 07:38

For that you can use either in nodejs layer. You need to convert the timestamp to date using below and use in the mysql query.

const date = new Date(1631514003973);
console.log(date)

For the use direct in mysql you can use mysql date function and

SELECT DATE_FORMAT(FROM_UNIXTIME(DATE(1631514003973)), '%e %b %Y') AS 'date_formatted' FROM table

How to convert a Timestamp into MySQL DateTime in JavaScript?

4 Answers4