jasonc Posted January 8, 2012 Share Posted January 8, 2012 I have stored my dates like so for years but now need to convert them to the TIME format `orignal_date` format is Y-m-d H:i:s and would like to have all of these converted to unix format and stored in `newdate` can this be done in phpmyadmin ? Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2012 Share Posted January 8, 2012 Why? You can format them on retrieval. There's no need to store the same data twice. Quote Link to comment Share on other sites More sharing options...
jasonc Posted January 8, 2012 Author Share Posted January 8, 2012 i have a backup of the DB but wanted to find a quicker way as there are 1000's of entries. and removing them and reverting back to previous would be annoying if i kept having to do it until it was right. once it is correct i would remove the old field. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2012 Share Posted January 8, 2012 But still, why not just continue to store the date in the RDBMS's native format? I can't think of a reason to store unix timestamps in MySQL, really. Quote Link to comment Share on other sites More sharing options...
jasonc Posted January 8, 2012 Author Share Posted January 8, 2012 the site has been completely redone and it now stores the date and time in this format so to make things easy i wanted to keep the same format throughout, and all the other entries that i have copied over from the old database had the old formated date/time so now none of these entries show up on the site at all because of it. all i want to do is convert them and remove the old formated date field once done. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2012 Share Posted January 8, 2012 It really sounds like a giant step backwards to me, but you can update it using UNIX_TIMESTAMP(). Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 8, 2012 Share Posted January 8, 2012 I hope your mysql timezone is set correctly and that your mysql timezone/DST database is up to date or you will mess up all your data. I also hope you don't have any dates older than 1970 or plan to use dates later than 2038, because the mysql Unix Timestamp storage and the mysql conversion functions currently only work for that range of years. You took a huge step backwards by going from dates stored as fixed human readable values (i.e. a date/time stored as 2012-01-07 08:39:00 will always be that value) to Unix Timestamps, which are dependent on a relatively slow and ambiguous conversion. Converting a Unix Timestamp to its human readable value is dependent on the timezone setting and any DST offset and is affected by how up to date the timezone/DST database is - mysql and php each have their own timezone/DST database that must be kept up to date as locations have been redefining their DST start/stop dates. Using Unix Timestamps for your data storage also means that you cannot use any day/month/year grouping directly in any queries, so you must execute more/slower queries to manipulate data that is associated with any human readable day/month/year divisions. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2012 Share Posted January 8, 2012 It isn't going to matter, I suspect. It sounds like his mind is made up. It's going to be a giant headache in the future just to avoid editing the scripts and doing it the right way now. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2012 Share Posted January 8, 2012 Hey, if you want to be stubborn, just use a trigger and have a second column. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.