correctly formatting javascript date to MySQL

Question

I'm creating a JavaScript based calendar, using full-calendar.js as main backbone. So in other to insert a new event into a MySQL database, I have the following code snippet:

$.post("http://localhost/calendar/index.php/calendar/insert_event", 
      { 
        title  : title,
        start  : start,
        end    : end,
        allDay : allDay,
        url    : ''
      }, 
      function(answer) {
        console.log(answer);
      }
);

the start and end dates are simply Date() objects:

var date = new Date();
var d = date.getDate();
var m = date.getMonth();
var y = date.getFullYear();

in the calendar.php controller, I get the following output:

{"title":"lunch",
 "start":"Tue Oct 08 2013 08:00:00 GMT-0700 (Pacific Standard Time)",
 "end":"Tue Oct 08 2013 08:30:00 GMT-0700 (Pacific Standard Time)",
 "allDay":"false",
 "url":""}

start and end are DATETIME Types in a MySQL table where the columns have the same type was above. When I do the insert using CodeIgniter's Active Record functions, it inserts to the table without further problems. However, when I look at the MySQL database to see the output, I see:

mysql> select * from calendar_utility;
+----+-----+-------+---------------------+---------------------+--------+
| id | url | title | start               | end                 | allday |
+----+-----+-------+---------------------+---------------------+--------+
|  1 |     | lunch | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |      0 |
+----+-----+-------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

How can I correct format the JavaScript Date() to insert correctly at the MySQL db?

forgive me, I was wrong, `strtotime` doesn't work as nice :) Just convert Date object to mysql datetime string. [Example](http://stackoverflow.com/questions/2280104/convert-javascript-to-date-object-to-mysql-date-format-yyyy-mm-dd) — Peter, Oct 10 '13 at 19:36
That has been done before, take a look a this post: http://stackoverflow.com/a/13452892/2131277 — edwardbrosens, Oct 10 '13 at 19:48

TheWolf · Answer 1 · 2013-12-24T22:02:29.280

I would probably convert the JS Date objects to strings conforming to the MySQL DATETIME format like this:

$.post("http://localhost/calendar/index.php/calendar/insert_event", 
      { 
        title  : title,
        start  : start.getFullYear() + "-" + (start.getMonth()+1) + "-" + start.getDate() + " " + start.getHours() + ":" + start.getMinutes() + ":" + start.getSeconds(),
        end    : end.getFullYear() + "-" (end.getMonth()+1) + "-" + end.getDate() + " " + end.getHours() + ":" + end.getMinutes() + ":" + end.getSeconds(),
        allDay : allDay,
        url    : ''
      }, 
      function(answer) {
        console.log(answer);
      }
);

Not that there is a missing `+` concatenator between `-` and `(start.getMonth()+1)` that is causing an error. Stackoverflow won't allow me to make "edits" of less than 6 characters. — Tato Barilatti, Dec 23 '13 at 14:53

correctly formatting javascript date to MySQL

1 Answers1