0

I found very similar questions like MySQL convert datetime to Unix timestamp , How to convert DATETIME to TIMESTAMP in mysql? related to this question. But I don't know how to apply those answers to my question...

I have a column (varchar type and I can't change the datatype to date because of some technical limitation of CMS I used) called "start_date" in Students table. Dates are displayed as YYYY-MM-DD. I want to change them as timestamp .

I am looking something like, 

UPDATE students SET start_date= convert_this_to_timestamp (start_date);

So basically I need to convert something like 2012-12-30 to 1356843600 using MySQL.

I am the Most Stupid Person
  • 3,415
  • 9
  • 22
  • 48
  • 1
    Come on. If you're capable of formulating the question, you're surely capable of answering it. – Strawberry Feb 12 '18 at 08:19
  • 1
    Create a new numeric column and set it. Don't store your dates as text, and don't continue doing this by storing the UNIX timestamp values into the same column. – Tim Biegeleisen Feb 12 '18 at 08:21
  • @TimBiegeleisen Actually this is for a WordPress site.. I use columns name table names as just an example... I can't change any column type or anything since thats how WordPress comes... But to write understandble question I use an example... But i want to run this query on WordPress wp_postmeta table since Date is saved as custom fild... But I need to convert them to timestamp... – I am the Most Stupid Person Feb 12 '18 at 08:25
  • Please try this? `UPDATE students SET start_date_timestamp= UNIX_TIMESTAMP (start_date) LIMIT 1;` If it works for the one row as expected, remove the limit and run for the rest of the records. Create a new field `start_date_timestamp` with `integer` type for this. – Biju P Dais Feb 12 '18 at 08:27

1 Answers1

2

as @Tim Biegeleisen said, you need to add a new numeric column:

UPDATE students SET start_date_ts = UNIX_TIMESTAMP(STR_TO_DATE(start_date, '%Y-%m-%d'));
claudiu.nicola
  • 311
  • 2
  • 9