Page 1 of 1

Changing a field from DATETIME to TIMESTAMP

Posted: Sat Mar 28, 2009 2:51 pm
by JellyFish
I'm trying to change one of my fields in my table from a DATETIME type to a TIMESTAMP type. But for some stupid reason the data in the field is still in the format 0000-00-00 00:00:00. Unless it's just displayed that way in phpMyAdmin... (This is the point in writing this post whrere I go and tests things out with php) After I tested things out I came to the conclusion that it's not just phpMyAdmin; the data stored in the date field is still in the unwanted format.

So I tried creating a new field all together, and still the same results.

Why must changing a MySQL table's fields be so damn confusing?!

I just want a date field that's in timestamp format, that is, an integer. Why do I want this? Because I have to convert the DATETIME into a timestamp in order to use the php date() function anyway.

So why when I change my date field to a TIMESTAMP or create an entirely new field with a TIMESTAMP type the data in the fields are still in that <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> format!? Stressful...

How could I change my date field to a TIMESTAMP integer?

Thanks for reading, and hopefully your not offended by my ranting either. :P

[EDIT] Sense I don't see any advantages with DATETIME type and I do with TIMESTAMP, I'm trying to change the type. The advantage that TIMESTAMP has over DATETIME is that I don't have to use the strtotime() php function ever time I want to reformat a date using the date() php function. If you know of any reasons why I should stick with DATETIME, then please point them out.

Re: Changing a field from DATETIME to TIMESTAMP

Posted: Sat Mar 28, 2009 4:13 pm
by John Cartwright
The main advantage of using a datetime field is you can more easily use the mysql date functions. Not to mention you can convert your datetime field to unix timestamp as needed, using:

Code: Select all

 
SELECT UNIX_TIMESTAMP(`datetime_column`) AS `timestamp`
...

Re: Changing a field from DATETIME to TIMESTAMP

Posted: Sat Mar 28, 2009 5:15 pm
by Bill H
How are you looking at the values? TIMESTAMP columns are stored as numbers but are usually returned as a string in a date format. The date format depends on the MySQL version.
From the MySQL docs:
In MySQL 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. Display widths (used as described in the following paragraphs) are no longer supported; the display width is fixed at 19 characters. To obtain the value as a number, you should add +0 to the timestamp column.

Re: Changing a field from DATETIME to TIMESTAMP

Posted: Sat Mar 28, 2009 9:36 pm
by JellyFish
I guess another great thing about DATETIME is that it can hold longer dates and it's not effected by the Y2K38, right?

I think I'm going to stick with DATETIME then. But I wouldn't have gone through all this trouble if I would have known about UNIX_TIMESTAMP MySQL function. Is there any other time functions I should know about? What about a MySQL function that converts a DATETIME into a timestamp?

Re: Changing a field from DATETIME to TIMESTAMP

Posted: Sat Mar 28, 2009 9:43 pm
by John Cartwright
JellyFish wrote:Is there any other time functions I should know about? What about a MySQL function that converts a DATETIME into a timestamp?
Yes, there are a lot. See http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
What about a MySQL function that converts a DATETIME into a timestamp?
FROM_UNIXTIME()

Re: Changing a field from DATETIME to TIMESTAMP

Posted: Tue Apr 07, 2009 3:27 am
by larsen
To convert date to MySQL Date and vice versa is very easy with date() and strtotime() function.

Code: Select all

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );
Check it out here:
http://n1scripts.com/php_scripts/php_co ... ipts&id=21