Default time & date for mySql datetime field

Posted on 2003-10-26
Last Modified: 2013-12-12
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! :)

Question by:bloodtrain
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +5

Expert Comment

ID: 9625318
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.

LVL 14

Expert Comment

ID: 9627833

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

Author Comment

ID: 9632302
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. :)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 9632329
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!

Author Comment

ID: 9632345
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.

Accepted Solution

DoppyNL earned 50 total points
ID: 9632373
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.

Author Comment

ID: 9632394
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.

Expert Comment

ID: 12470232
I stumbled across this question looking for an answer myself...

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

Expert Comment

ID: 13186609
"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...

Expert Comment

ID: 13308590
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?
LVL 14

Expert Comment

ID: 13322425
finofontana, the solution is to use the special field type TIMESTAMP. check MySQL documentation.

Expert Comment

ID: 13595697

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.

Expert Comment

ID: 13596664
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.

Expert Comment

ID: 25110718
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');

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

736 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question