0

I have a MYSQL database with a VARCHAR column called date_of_item. In date_of_item are PHP timestamps generated with the strtotime function. The PHP timestamps can either be NULL where no date is known, the VARCHAR value zero (unix epoch), or a positive/negative value indicating the date (some dates are before the epoch).

I want to change the value of the column to be simply a string representation of the date in the form dd mmm yyyy and treat it as a simple string (not all the dates are known exactly, some are just months and some are years... so a timestamp isn't really appropriate here).

I have looked at the FROM_UNIXTIME and DATE_FORMAT functions in SQL but they don't seem to be able to deal with the negative timestamps used in PHP.

Is there a way to UPDATE the table and change the value of the column taking into account the negative values of the PHP timestamps?

Pseudocode for the query would be something along the lines of:

UPDATE table_name SET date_of_item = FROM_UNIXTIME_WITH_NEGATIVE_DATES(date_of_item, "%d %b %Y") WHERE date_of_item IS NOT NULL
ose
  • 4,065
  • 2
  • 24
  • 40
  • Not a duplicate - there is more to this question (formatting the date, updating the original table in-place) – ose Mar 11 '16 at 13:09
  • The core of the question is the same "Is there a way to ... change the value of the column taking into account the negative values of the PHP timestamps?" The update-in-place is trivial. – Patrick Q Mar 11 '16 at 13:12

0 Answers0