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.

CodePudding user response：

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);

CodePudding user response：

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);

CodePudding user response：

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

CodePudding user response：

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);