MySQL and JavaScript Dates

[TheExcession]

Go gently on me, I'm not very good doing SQL....

I have a MySQL DateTime Column in a table.

I'm using some java script to get the current time, and put it into a text box that is then posted to the Server for an insert into the table.

Java Script::
var d = new Date();
document.response_form.date.value = d.getTime();

This gives me a millisecond count which I don't think MySQL likes:

SQL Snippet:

INSERT INTO events (date_time_column, ...) VALUES ( '1174655427734', ... );

Is there a function in MySQL to convert this number into a time format that can go into the table. Or should I convert it at the JavaScript side.

I'd prefer to do it at the MySQL side if possible.

thanks
Ex

[Don]

We ended up writing a comprehensive date/time format conversion library in VBScript, JavaScript and T/SQL (SQL Servers stored procedure language).

As dates pass "boundaries" you often get month/day/year and day/month/year trials and tribulations.

We decided on a Date format for our app (and a Time format for that matter) and wrote a pile of conversion routines and validation routines that force anything anyone types into the proper format. Thereafter we pass the date around as a string until its finally cast into a "smalldatetime" when its inserted into a SQL Table.

Any other approach seemed to lend itself to going wrong.

[Don]

For your specific problem?

Turn the date/time into a suitable string in JavaScript. Pass it as text back to whatever is handling it on the server side. If that's an ASP/ASPX page then you just create your SQL there and put the text value straight into the SQL statement or if you have some middleware you could create the SQL in JavaScript and send it straight to the DB.

[slapmatt]

Do you have to parse the date/time though from the JavaScript? Can you just update it with the time from a MySQL function?

So:

INSERT INTO events (date_time_column, ...) VALUES ( NOW(), ... );

www.databasejournal.com/features/mysql/article.php/2172731

[TheExcession]

I'll be doing quite a lot of POST Requests to the Server (at the moment approximately 1 million every 15 days - so about 1 a second) - but this load is set to increase rapidly so I really want to keep the POST VARs as short as possible in the HTTP POST request, hence my initial use of the millisecond count.

Forgot to mention it's PHP at the Server, so I'll look at a few date functions from within that.

However I'm still not clear what value is that MySQL requires in an SQL insert for a DateTime column though - will a GMT formatted string work?