datetime

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

datetime

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

Post 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?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Sorry I read date field not datetime field. You can find the answer to your question here:
http://php.net/date

Mac
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The manual has the answer:
http://www.mysql.com/doc/en/DATETIME.html

Mac
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
mikusan
Forum Contributor
Posts: 247
Joined: Thu May 01, 2003 1:48 pm

Post 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...
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply