2 Replies - 6235 Views - Last Post: 29 July 2012 - 05:22 PM

#1 rumbitas   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 98
  • Joined: 07-December 10

[QUESTION] How I can convert the values

Posted 29 July 2012 - 07:57 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: [QUESTION] How I can convert the values

#2 Martyr2   User is online

  • Programming Theoretician
  • member icon

Reputation: 5207
  • View blog
  • Posts: 13,955
  • Joined: 18-April 07

Re: [QUESTION] How I can convert the values

Posted 29 July 2012 - 10:46 AM

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. :)
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: [QUESTION] How I can convert the values

Posted 29 July 2012 - 05:22 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1