Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107413
  • Last Modified:

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

Thanks!
Tony
0
bloodtrain
Asked:
bloodtrain
  • 3
  • 3
  • 2
  • +5
1 Solution
 
DoppyNLCommented:
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: http://www.mysql.com/doc/en/Column_types.html

Best solution depends on what you want.

0
 
ThGCommented:

Agree. I think there aren't other ways to do that.
0
 
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. :)
0
Technology Partners: 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!

 
DoppyNLCommented:
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!
http://www.mysql.com/doc/en/Column_types.html
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!
0
 
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.
0
 
DoppyNLCommented:
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.
0
 
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.
0
 
mixartCommented:
I stumbled across this question looking for an answer myself...

If you put 'timestamp' into the default value it should do the trick.
0
 
nentwinedCommented:
"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...
0
 
finofontanaCommented:
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?
0
 
ThGCommented:
finofontana, the solution is to use the special field type TIMESTAMP. check MySQL documentation.
0
 
Connick007Commented:
finofontana,

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.
0
 
finofontanaCommented:
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.
0
 
e-wiZzCommented:
lol its simple man,
<?php
$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');
?>
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now