Default time & date for mySql datetime field

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! :)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Giovanni GCTOCommented:

Agree. I think there aren't other ways to do that.
bloodtrainAuthor Commented:
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. :)
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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!
bloodtrainAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bloodtrainAuthor Commented:
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?
Giovanni GCTOCommented:
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');
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.