0

I have a mysql users table with column join_date in a format "d/m/Y". I just want to update this column to a timestamp of that existed data.

id    name    join_date
1     john    08/02/2014

Now I need a code to run (like while loop or foreach) to update all dates data in column join_date to a timestamp [time():] as below:

id    name    join_date
1     john    1391814000



Any suggestion guys? Thanks in advance.

Kermit
  • 33,827
  • 13
  • 85
  • 121
sohal07
  • 440
  • 8
  • 21

1 Answers1

3

In the comments we could find out that not TIMESTAMP but DATE is the best datatype for your application. Assuming that join_date is a varchar column, you can issue the following SQL commands in order to change the column type and convert the data.

First you need to add a new, temporary, column:

ALTER TABLE `users` ADD `temp_date` DATE;

Then convert the existing strings to DATE values:

UPDATE `users` SET `temp_date` = STR_TO_DATE(`join_date`, '%c/%e/%Y');

Now you can drop the existing column:

ALTER TABLE `users` DROP `join_date`

And finally rename the temporary column:

ALTER TABLE `users` CHANGE `temp_date` `join_date` DATE;
hek2mgl
  • 152,036
  • 28
  • 249
  • 266
  • was a bug in there, you need to use `STR_TO_DATE()`. Now it should work. – hek2mgl Feb 08 '14 at 13:34
  • Why are you storing dates in a Unix timestamp? You're adding an extra step any time you will need to work the dates. – Kermit Feb 08 '14 at 13:35
  • Question was how to convert the column types. I don't know the application scenario. There will be pro's and con's for either timestamps or datetime|date, depending on the requirements. isn't it? – hek2mgl Feb 08 '14 at 13:39
  • Sorry, it was directed at @sohal07. And to answer your question, you shouldn't use timestamp. [Read this](http://stackoverflow.com/questions/2533767/mysql-whats-the-best-to-use-unix-timestamp-or-datetime) – Kermit Feb 08 '14 at 13:40
  • @FreshPrinceOfSO Thanks for the link. I don't think that the links says, that TIMESTAMP should not being used. It just shows the differences. For example, althought it doesn't count in most real life scenarios today, you see that they require the half of the memory than a DATETIME – hek2mgl Feb 08 '14 at 13:46
  • @FreshPrinceOfSO: I don't specifically want to store date in unix timestamp. I simply want date to be stored in a way that in future i can find out +1 week or so on.... So I thought I should start storing the dates in time() format in future. But at this time, I need a code to update all those dates already stored in database. – sohal07 Feb 08 '14 at 13:47
  • You should use the DATE format. Will change my answer – hek2mgl Feb 08 '14 at 13:48
  • @sohal07 The way you add one week or so on is using the date functions. Don't convert your columns because you don't know how to use them. – Kermit Feb 08 '14 at 13:56
  • 1
    @sohal07 I have update the post. If you now want to select for example every join_date at is maximum 1 week ago, then use: `SELECT * FROM users WHERE join_date > NOW() - INTERVAL 1 WEEK` – hek2mgl Feb 08 '14 at 13:59
  • No problem. :) thanks to @FreshPrinceOfSO, for helping to find out that `DATE` is the right datatype for your application. – hek2mgl Feb 08 '14 at 14:09