Solved

Default time & date for mySql datetime field

Posted on 2003-10-26
14
107,347 Views
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! :)

Thanks!
Tony
0
Comment
Question by:bloodtrain
  • 3
  • 3
  • 2
  • +5
14 Comments
 
LVL 6

Expert Comment

by:DoppyNL
Comment Utility
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
 
LVL 14

Expert Comment

by:ThG
Comment Utility

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

Author Comment

by:bloodtrain
Comment Utility
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
 
LVL 6

Expert Comment

by:DoppyNL
Comment Utility
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
 
LVL 2

Author Comment

by:bloodtrain
Comment Utility
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
 
LVL 6

Accepted Solution

by:
DoppyNL earned 50 total points
Comment Utility
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
 
LVL 2

Author Comment

by:bloodtrain
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Expert Comment

by:mixart
Comment Utility
I stumbled across this question looking for an answer myself...

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

Expert Comment

by:nentwined
Comment Utility
"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
 

Expert Comment

by:finofontana
Comment Utility
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
 
LVL 14

Expert Comment

by:ThG
Comment Utility
finofontana, the solution is to use the special field type TIMESTAMP. check MySQL documentation.
0
 
LVL 1

Expert Comment

by:Connick007
Comment Utility
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
 

Expert Comment

by:finofontana
Comment Utility
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
 

Expert Comment

by:e-wiZz
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now