Msg#: 4119806 posted 3:44 am on Apr 22, 2010 (gmt 0)
I have created an HTML form and I have it so the data from the form is entered into a database and stored - the one thing I cannot seem to do is get it to automatically insert the date and time of the form submission into the database.
I have a field called postdate in my database with a type of date - what do I need to do to get the post submission date and time put into that field? I would prefer it as an actual date and time which I can read rather than using seconds passed since 1970 or whatever it is.
Jason (living proof that a little knowledge is dangerous!)
Msg#: 4119806 posted 4:27 am on Apr 22, 2010 (gmt 0)
rather than using seconds passed since 1970 or whatever it is.
This "kind of" depends on how your postdate field is created.
Alter table your_table change postdate postdate datetime not null default '0000-00-00 00:00:00';
There. Fixed it for you. :-) If you don't like that format, see below.
what do I need to do to get the post submission date and time put into that field?
Add this into the other values you submit to the DB:
insert into your_table (postdate) values(now());
Done. This will put your mysql server's time as of now into the field. There are cases where the server time is different than the mysql server time, and if it is . . . complain, it needs to be fixed (unless they are geographically different, 'nother story.)
If your server is in a different time zone than you expect, you may have to adjust, topic for another thread.
For the precise formatting you want, have a look at the date_format() section of the date and time functions [dev.mysql.com] manual page.
You'd just select the date format you want when selecting them:
select date_format(postdate, '%b %d %Y %r') from your_table;
--> Apr 21 2010 09:27:45 PM
The cool thing is, you can have a unix timestamp field as a date field, and those functions will work as well as they do on a formatted datetime field.