Default time & date for mySql datetime field

bloodtrain used Ask the Experts™
In MS Access, you can put "Now()" in a date field and it will automatically insert the date and time when you add a record to the db. Is there something similar for MySql? I have a datetime field which I want to insert the date & time automatically cuz I'm too lazy to stick in one more line of code! :)

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

you can simply use "now()" in the insert/update query to insert the current date.

Or, if you don't want to do that either, set the default value of the field to "now()", then when inserting a new record you don't have to mention the field and mysql will insert the current date.

Another possibility is to use the fieldtype timestamp wich will change even when using update query's.
More info about the timestamp field:

Best solution depends on what you want.

Agree. I think there aren't other ways to do that.


I tried adding the now() for the default but it doesn't work.  It always puts: 0000-00-00 00:00:00

I basically just want to know if its possible to automatically insert the date. I'm not interested in using timestamp or some other method outside of mySql. There must be a way - geez, if Access can do it, I'm sure mySql can do it. :)
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

hmm, could be my mistake that you can't define a default value with a function of mysql :|  
sorry about that.

note: timestamp IS mysql!!

it's actually the same type as datetime, only with some extra functionality combined to it so that mySQL will automaticly update the field to the current date+time in some situations.
See the documentation of mySQL for more details on that, is it would be foolish for me to repeat that here!
scroll a little down to the timestamp type, it will explain it all.

You are looking for something that will automaticly update a date+time field when the record is updated.
the fieldtype timestamp does just that!


no, i'm not looking for something that will automatically update a date+time when record is updated. I'm looking for a one-time only. For example, a "Date Registered" field in a user table. I don't want that value updated everytime an update is done to a user.

I am familar with timestamp - I just wanted to know if my question was possible.  I guess not.
Then we were obviously thinking towards a different goal.
Timestamp won't work then indeed.

Best way then would probably be to pass now() with the insert query.

There could be something else though.
If you declare 2(!) fields with the type timestamp
mysql will set the first field to the current date+time on creation of the record.
and set the second field to the current date+time on creation and with every update of the record.

wich would in fact result in 2 fields:
- register date                (set on creation, and not changed automaticly)
- last update date            (set on creation, and updated on every update-query)
wich could be quite usefull with a user database :)

Do check the mysql manual (same link again) on that though, could be that it works a *little* different then I just said.


I thank you for helping me but I don't think you understand what I meant.  I'm not looking for another solution whatsoever other than using only datetime. I do not want to use a query string to isnert the date and I don't want to use timestamp.  I was trying to do a direct comparison with MS Access.

It seems that it cannot be done and thats all.  Thank you for your efforts and I will give u the points anyway.

I stumbled across this question looking for an answer myself...

If you put 'timestamp' into the default value it should do the trick.
"If you put 'timestamp' into the default value it should do the trick."

I tried:

create table temp (
  stamp datetime not null default timestamp

and got an error: #1067 - Invalid default value for 'stamp'

create table temp (
  stamp datetime not null default 'timestamp'

and got '0000-00-00' for the value, after insert

I'm really used to being able to do 'now()' with postgresql.  would rather not have to add now() to every insert.  but such is life, I suppose.  not _too_ big a deal.  And yeah, I don't want to use timestamp, autoupdating on every update...
Hi All,

Has anyone found a solution for this problem yet? I've tried everything for the default value in a DateTime field: now(), CURRENT_TIMESTAMP, CURRENT_DATETIME, CURRENT_TIMESTAMP(), CURRENT_DATETIME(), timestamp(), datetime(), etc, etc,. All solutions end with '0000-00-00 00:00:00' as default value. Is there really no solution, or am I overlooking something?
finofontana, the solution is to use the special field type TIMESTAMP. check MySQL documentation.

Here is what worked for me.  I too couldn't seem to get the TimeStamp field working.

I created a DATETIME field ($DateModified) in my table.  Then whenever I wanted to update the field I set it to $DateModified = date("y/m/d : H:i:s", Time()).  That would set the $DateModified field to the current Date/Time.  The data is stored in the "YYYY-MM-DD HH:MM:SS" format.

For display purposes, I just used variations of the date function to display the format I wanted.  But before you can use the date() function you have to convert the date from the database into a timestamp using the strtotime() function.  This is because the date() function requires a timestamp for input and the data seemed to be stored in a string format.  Here is my code.

// Set the current date/time before updating the DB
$DateModified = date("y/m/d : H:i:s", Time());

//Convert to timestamp before displaying
date("M d, Y : h:i a", strtotime($DateModified))

I'm sure this is the "manual" way to do this, but it's the only way I could get it to work for me.  It's been my experience that sometimes being able to use "best practices" requires being with the latest versions of mySQL and PHP.  Hope this helps.
Thanks for the advices. In the end I solved it this way: I have a field of type TIMESTAMP with default value of
CURRENT_TIMESTAMP. This will insert the current date and time when a record is created.

lol its simple man,
$time = date("m.Y.d") ;//month-year-day
$time2 = date("h:mm:s");//hours-minutes-seconds
$sql = mysql_query("INSERT INTO something(date,date2) values('$time','$time2');

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial