[rumbitas]

Hi, I have a table with 3 columns, with data like that:

2009/01/01 00:00,2009/01/01 00:00,0.49122
2009/01/01 00:01,2009/01/01 00:01,0.48858
2009/01/01 00:02,2009/01/01 00:02,0.48834
2009/01/01 00:03,2009/01/01 00:03,0.48828
2009/01/01 00:04,2009/01/01 00:04,0.48822

The dates are stored like this 2009/01/01 00:00. I want to know how I can convert this values to a timestamp in the phpmyadmin. I suppose that the format must be converted too.

Thanks so much.

[Martyr2]

Take a look at the Timestamp function. One version of it takes a single argument which is the date and time argument and then converts that to a timestamp.

Before doing anything, make sure you backup the database. Then I would try something like adding a new column as a timestamp format and use this timestamp function in an update query. After the update, you could drop the old column and possibly rename the new column to the old one.

update tablename set newfield = Timestamp(oldfield)

Would be my first try anyways.

[e_i_pi]

Personally, I would opt for retaining it as a datetime field, and then using native functions of FROM_UNIXTIME() and UNIX_TIMESTAMP() to convert back and forth. I'm a bit of a purist when it comes to datetime though, and am entirely against storing it as a unix timestamp. The reason I am against it is because when datetime values are stored as unix timestamps, it becomes laborious for the SQL engine to do datetime comparisons (e.g. BETWEEN, <, >), as you need to cast the timestamp value to datetime. It is also difficult to do GROUPing and partial datetime data extraction, such as getting all the users that signed up during the month of May, or finding out how many items were purchased during FY12.

The fact that MySQL specifically caters for PHP by having unixtime functions should not lead you to believe that storing datetime values as unix timestamps is a good idea. The database should be responsible for storing data in the format that is most suitable and easiest to work with, it shouldn't be stored in a convenient format for a single programming language.