How to convert seconds to datetime format?

Question

I have this variable var resetPasswordExpires = 1524133033085; and I want to convert seconds to datetime format to update database sql column. I did this

 var d = new Date();
 d.toString(resetPasswordExpires);

and i take d = Thu Apr 19 2018 12:21:02 GMT+0300 (GTB Daylight Time) and of course canot save column to database. Database column format is DATETIME

query :

UPDATE User SET resetPasswordToken = 
c2d313284f6de68cc1f18e3c45b0ea2c720220f0, resetPasswordExpires = Thu Apr 19 
2018 12:21:02 GMT+0300 (GTB Daylight Time) WHERE UserID = 21;

I *suspect* that what you actually have in hand is the number of *milliseconds* since 1970-01-01. So the first thing I'd suggest is that you actually clear up what that number is *meant* to be/represent. — Damien_The_Unbeliever, Apr 19 '18 at 09:36
Yes, the number is the result of var resetPasswordExpires = Date.now() + 3600000; I want to save this variable in database — devRena, Apr 19 '18 at 09:37
Isnt this more a C# question on how to format your datetime value you get outputted from your C# app? — SqlKindaGuy, Apr 19 '18 at 09:41

score 3 · Answer 1 · answered Apr 19 '18 at 09:36

3

One option would be to DATEADD the number of seconds in your value to the start of the UNIX epoch:

SELECT DATEADD(s, 1524133033085/1000, '19700101 00:00:00:000');

19.04.2018 10:17:13

Demo

Note that I divide your value by 1000 because it is in milliseconds.

answered Apr 19 '18 at 09:36

Tim Biegeleisen

502,043
27
286
360

And/or just add the milliseconds instead of seconds instead of dividing by 1000? There is an ‘ms’ option for ‘dateadd’ if I remember correctly. – thomas Apr 19 '18 at 11:50
@thomas I wasn't sure of that, and you know how SO is, post quickly or lose. – Tim Biegeleisen Apr 19 '18 at 11:53
I hear ya! Just checked and there is a millisecond, microsecond, and nanosecond option. – thomas Apr 19 '18 at 11:55

score 1 · Answer 2 · edited Apr 23 '18 at 08:26

1

I guess this is already answered in Convert JS date time to MySQL datetime. Please validate your requirement.

var resetPasswordExpires = 1524133033085;
var d = new Date(1524133033085).toISOString().slice(0, 19).replace('T', ' ');

edited Apr 23 '18 at 08:26

Zoe

27,060
21
118
148

answered Apr 19 '18 at 10:01

AmitD

11
6

Thank you very much! that is exactly i wanted. – devRena Apr 19 '18 at 10:16

Hardik Masalawala · Answer 3 · 2018-04-19T12:29:14.677

TESTED and SOLVED

Convert your timestamp as following

try in Console

var resetPasswordExpires = 1524133033085;
var d = new Date();
d.toString(resetPasswordExpires);
var passDateToISOString  = d.toISOString();
passDateToISOString

Result in console

"2018-04-19T09:55:14.824Z"

then Pass passDateToISOString variable value to your sql

for Example ( try in Sql )

UPDATE User 
SET resetPasswordToken = c2d313284f6de68cc1f18e3c45b0ea2c720220f0, 
resetPasswordExpires = '2018-04-19T09:55:14.824Z' 
WHERE UserID = 21;

How to convert seconds to datetime format?

3 Answers3

Demo