Hi, I have a table in my database called "listingsdbelements" and each property has an available date which is in 'datetime' ie '1200268800'
I need to increase every one by exactly 1 year.
Within the table there is a column called "listingsdbelements_field_name" so when the value of "listingsdbelements_field_name" is "available" there is a value in the next column "listingsdbelements_field_value" with the date time value ie "1200268800".
Can this be done?
What is the date value of '1200268800'?
1200268800 is 14/01/2008
I am unsure if the date format matters but I use DD/MM/YYYY
Hi, I know its unix time, I would like to add 1 year ie (60x60x24x36) in seconds to each value. So is there an sql statement I can add which does this for the values and database model I mention above?
ie.
To get 1 year in front of 1200268800 I would need to add 3110400 which then translates 14/01/2008 into 14/01/2009
Im not too bothered about leap years I just need to add in BULK to the 800 properties which have a unix datetime.
Assuming this is SQL Server, and that I have understood the table / column name requirements correctly!!, you should be able to do this with
UPDATE U SET listingsdbelements_field_value = listingsdbelements_field_value + 3110400 FROM listingsdbelements AS U WHERE listingsdbelements_field_name = 'available'
Take a backup first!
Hi Kristin, Thanks for posting this. I ran the SQL statement and received an error back
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM default_en_listingsdbelements AS U WHERE default_en_listingsdbelements_fie' at line 3 "
I am unsure of the exact SQL version but I know it has recently been upgraded on my server.
I added default_en_ to each statment prefix too..
UPDATE listingsdbelements
SET listingsdbelements_field_value = listingsdbelements_field_value + 3110400
WHERE listingsdbelements_field_name = 'available'
2 People are following this question.
