Page 1 of 1

datetime

Posted: Wed Jul 30, 2003 4:52 am
by mikusan
I have discovered the need to use the date and time on my website... I usually use

Code: Select all

$dt = getdate(); 
		$month = $dt['month']; 
		$monthnum = $dt['mday'];
		$wday = $dt['weekday']; 
		$year = $dt['year']; 
		$hours = $dt['hours']; 
		$minutes = $dt['minutes']; 
		$seconds = $dt['seconds'];
But i thought that making a new column in the MySQL table, and a variable of the type datetime would mean that the database will take care of the datetime stamps... unfortunately i have found it isn't so. So i ask how do i qury the database to insert the required datetime?

Do i ave to use my method above and do it manually or is there a function that will do it for me so that i don't have to edit it perfectly to let MySQL accept the format.

Posted: Wed Jul 30, 2003 5:00 am
by twigletmac
You can format a date for entry into MySQL by using the date() function:

Code: Select all

$mysql_date = date('Y-m-d');
If you're just putting the current date into MySQL though, you can use MySQL's NOW() function in the SQL statement.

Mac

Posted: Wed Jul 30, 2003 5:07 am
by mikusan
will doint the above fit well into mysql... i thought the format was something like y-m-d HH:MM:SS
How would i adapt what you said above to fit this?

Posted: Wed Jul 30, 2003 5:29 am
by twigletmac
Sorry I read date field not datetime field. You can find the answer to your question here:
http://php.net/date

Mac

Posted: Wed Jul 30, 2003 5:36 am
by mikusan
Thanks, now tell me does the datetime field accept ANY sort of date format that is can i choose it? I ask because before i set that datetime field to null it gave me a default setup that i don't remember but i am hoping that is just an example.

Posted: Wed Jul 30, 2003 5:51 am
by twigletmac
The manual has the answer:
http://www.mysql.com/doc/en/DATETIME.html

Mac

Posted: Wed Jul 30, 2003 6:27 am
by mikusan
Thanks man that really helps but i am still confused, according to that manual TIMESTAMP is the one to be used so that MySQL automatically sets Date and time. Now then DATETIME isn't set by MySQL per se but it is set by a query by PHP let's say.

This leaves me with the doubt of which one to use. I would fall towards Datetime because i don't want the datetime update at any update queries and only on the first INSERT query. But i would also like my own datetime format...

Any suggestions? Should i just make a varchar field and then insert a string like the following

Code: Select all

$datetime = date("F j, Y   G:i");
This would be my preferred way to store it, but i am afraid that it would become difficult later on to compare dates...is that so?

Thanks for the help even if i don't say so in the end so that i don't *bump* the post up.

Posted: Wed Jul 30, 2003 7:17 am
by twigletmac
Use a DATETIME field and store dates in MySQL's required format. Then when you want to display the date use MySQL's DATE_FORMAT() function (similar to PHP's date() function) to format it the way you want it. That way you get the benefits of proper date storage as well as the date formatted any way you like.

Mac

Posted: Wed Jul 30, 2003 7:47 am
by mikusan
Thanks alot... You really helped me out!

but one more thing how exactly do you use the MySQL DATE_FORMAT() function? Do i use it in PHP as a mysql_query or what i have never used it... thanks...

Posted: Wed Jul 30, 2003 8:03 am
by twigletmac
You use DATE_FORMAT in the SQL statement:
http://www.mysql.com/doc/en/Date_and_ti ... ml#IDX1314
For example:

Code: Select all

SELECT DATE_FORMAT('%M %e, %Y %k:%i', datefieldname) AS datefieldname FROM tablename
Mac