Convert JS date time to MySQL datetime

Question

Does anyone know how to convert JS dateTime to MySQL datetime  Also is there a way to add a specific number of minutes to JS datetime and then pass it to MySQL datetime

User · Answer

A simple solution is send a timestamp to MySQL and let it do the conversion  Javascript uses timestamps in milliseconds whereas MySQL expects them to be in seconds - so a division by 1000 is needed     Current date   time as a timestamp  let jsTimestamp   Date now          OR   a specific date   time as a timestamp  jsTimestamp   new Date  quot 2020-11-17 16 34 59 quot   getTime        Adding 30 minutes  to answer the second part of the question   jsTimestamp    30   1000      Example query converting Javascript timestamp into a MySQL date let sql    SELECT FROM UNIXTIME     jsTimestamp       1000  AS mysql date time

User · Answer

For arbitrary date string       Your default date object   var starttime   new Date       Get the iso time  GMT 0    UTC 0  var isotime   new Date  new Date starttime   toISOString         getTime   is the unix time value  in milliseconds     getTimezoneOffset   is UTC time and local time in minutes     60000   60 1000 converts getTimezoneOffset   from minutes to milliseconds   var fixedtime   new Date isotime getTime  - starttime getTimezoneOffset   60000       toISOString   is always 24 characters long  YYYY-MM-DDTHH mm ss sssZ      slice 0  19  removes the last 5 chars    sssZ  which is  UTC offset       replace  T        removes the pad between the date and time  var formatedMysqlString   fixedtime toISOString   slice 0  19  replace  T         console log  formatedMysqlString      Or a single line solution   var formatedMysqlString    new Date   new Date  new Date new Date     toISOString      getTime   -   new Date    getTimezoneOffset   60000    toISOString   slice 0  19  replace  T         console log  formatedMysqlString      This solution also works for Node js when using Timestamp in mysql    Gajus Kuizinas s first answer seems to modify mozilla s toISOString prototype

User · Answer

I think the solution can be less clunky by using method toISOString    it has a wide browser compatibility   So your expression will be a one-liner   new Date   toISOString   slice 0  19  replace  T           The generated output       2017-06-29 17 54 04

User · Answer

The venerable DateJS library has a formatting routine  it overrides   toString       You could also do one yourself pretty easily because the  Date  methods give you all the numbers you need

User · Answer

The easiest correct way to convert JS Date to SQL datetime format that occur to me is this one  It correctly handles timezone offset    const toSqlDatetime    inputDate    gt        const date   new Date inputDate      const dateWithOffest   new Date date getTime   -  date getTimezoneOffset     60000       return dateWithOffest          toISOString            slice 0  19           replace  T           toSqlDatetime new Date       2019-08-07 11 58 57 toSqlDatetime new Date  2016-6-23 1 54 16       2016-06-23 01 54 16   Beware that  Paulo Roberto answer will produce incorrect results at the turn on new day  i can t leave comments   For example   var d   new Date  2016-6-23 1 54 16        finalDate   d toISOString   split  T   0      d toTimeString   split      0   console log finalDate      2016-06-22 01 54 16    We ve got 22 June instead of 23

User · Answer

Simple  just Replace the T  Format that I have from my  lt input class  quot form-control quot  type  quot datetime-local quot    is    quot 2021-02-10T18 18 quot  So just replace the T  and it would look like this   quot 2021-02-10 18 18 quot  SQL will eat that  Here is my function  var CreatedTime   document getElementById  quot example-datetime-local-input quot   value  var newTime   CreatedTime replace  quot T quot    quot   quot    Reference  https   www tutorialrepublic com faq how-to-replace-character-inside-a-string-in-javascript php    text Answer 3A 20Use 20the 20JavaScript 20replace the 20global 20  20g 20  20modifier  https   www tutorialrepublic com codelab php topic faq amp file javascript-replace-character-in-a-string

User · Answer

new Date   toISOString   slice 0  10   quot   quot  new Date   toLocaleTimeString  en-GB

User · Answer

While JS does possess enough basic tools to do this  it s pretty clunky          You first need to create a formatting function to pad numbers to two digits         function twoDigits d        if 0  lt   d  amp  amp  d  lt  10  return  0    d toString        if -10  lt  d  amp  amp  d  lt  0  return  -0     -1 d  toString        return d toString                 and then create the method to output the date string as desired     Some people hate using prototypes this way  but if you are going    to apply this to more than one Date object  having it as a prototype    makes sense       Date prototype toMysqlFormat   function         return this getUTCFullYear      -    twoDigits 1   this getUTCMonth       -    twoDigits this getUTCDate            twoDigits this getUTCHours            twoDigits this getUTCMinutes            twoDigits this getUTCSeconds

User · Answer

Solution built on the basis of other answers  while maintaining the timezone and leading zeros  var d   new Date   var date         d getFullYear          00    d getMonth     1  slice -2         00    d getDate     1  slice -2    join  -     var time           00    d getHours    slice -2         00    d getMinutes    slice -2         00    d getSeconds    slice -2    join        var dateTime   date         time  console log dateTime     2021-01-41 13 06 01

User · Answer

var  t   new Date      if you want UTC format simply   t toLocaleString  indian     timeZone   UTC     replace    w      w      w      w       3- 2- 1  4      or   t toISOString   slice 0  19  replace  T           and if want in specific timezone then    t toLocaleString  indian     timeZone   asia kolkata     replace    w      w      w      w       3- 2- 1  4

User · Answer

Using toJSON   date function as below   x000D   x000D  var sqlDatetime   new Date new Date   getTime   - new Date   getTimezoneOffset     60   1000  toJSON   slice 0  19  replace  T         console log sqlDatetime   x000D   x000D   x000D

User · Answer

JS time value for MySQL  var datetime   new Date   toLocaleString      OR   const DATE FORMATER   require   dateformat     var datetime   DATE FORMATER  new Date     yyyy-mm-dd HH MM ss       OR  const MOMENT  require   moment     let datetime   MOMENT   format   YYYY-MM-DD  HH mm ss 000       you can send this in params its will work

User · Answer

Full workaround  to mantain the timezone  using  Gajus answer concept   var d   new Date        finalDate   d toISOString   split  T   0      d toTimeString   split      0   console log finalDate     2018-09-28 16 19 34 --example output

User · Answer

I m using this long time and it s very helpful for me  use as you like  Date prototype date function         return this getFullYear    -  String this getMonth   1  padStart 2   0    -  String this getDate    padStart 2   0      Date prototype time function         return String this getHours    padStart 2   0       String this getMinutes    padStart 2   0       String this getSeconds    padStart 2   0      Date prototype dateTime function         return this getFullYear    -  String this getMonth   1  padStart 2   0    -  String this getDate    padStart 2   0       String this getHours    padStart 2   0       String this getMinutes    padStart 2   0       String this getSeconds    padStart 2   0      Date prototype addTime function time        var time time split          var rd new Date this setHours this getHours   parseInt time 0         rd new Date rd setMinutes rd getMinutes   parseInt time 1         return new Date rd setSeconds rd getSeconds   parseInt time 2        Date prototype addDate function time        var time time split  -       var rd new Date this setFullYear this getFullYear   parseInt time 0         rd new Date rd setMonth rd getMonth   parseInt time 1         return new Date rd setDate rd getDate   parseInt time 2        Date prototype subDate function time        var time time split  -       var rd new Date this setFullYear this getFullYear  -parseInt time 0         rd new Date rd setMonth rd getMonth  -parseInt time 1         return new Date rd setDate rd getDate  -parseInt time 2         and then just   new Date   date     which returns current date in  MySQL format   for add time is  new Date   addTime  0 30 0     which will add 30 minutes     and so on

User · Answer

var date  date   new Date    date   date getUTCFullYear      -          00     date getUTCMonth   1   slice -2     -          00    date getUTCDate    slice -2                 00    date getUTCHours    slice -2                 00    date getUTCMinutes    slice -2                 00    date getUTCSeconds    slice -2   console log date     or even shorter   new Date   toISOString   slice 0  19  replace  T           Output   2012-06-22 05 40 06   For more advanced use cases  including controlling the timezone  consider using http   momentjs com    require  moment     format  YYYY-MM-DD HH mm ss      For a lightweight alternative to momentjs  consider https   github com taylorhakes fecha  require  fecha   format  YYYY-MM-DD HH mm ss

User · Answer

I have given simple JavaScript date format examples please check the bellow code    var data   new Date   now        without jquery remove this   now   console log data    Thu Jun 23 2016 15 48 24 GMT 0530  IST   var d   new Date      dformat    d getFullYear    d getMonth   1                 d getDate                    join  -                       d getHours                   d getMinutes                   d getSeconds    join        console log dformat    2016-6-23 15 54 16   Using momentjs  var date   moment   format  YYYY-MM-DD H mm ss     console log date     2016-06-23 15 59 08   Example please check https   jsfiddle net sjy3vjwm 2

[javascript] Convert JS date time to MySQL datetime

Examples related to javascript

Examples related to mysql