![]() ![]() in mysql Yeilds this timestamp 1111892400 ![]() My code and Result SELECT UNIX_TIMESTAMP(' 03:00:00') The documentation code and result mysql> SELECT UNIX_TIMESTAMP(' 03:00:00') The website shows a simple way to create a UNIX_TIMESTAMP Just to test my hypothesis I did an example I found on MySQL. Is there anything to do with timezone correction that could be causing this? The time appears off every time I convert something to UNIX_Timestamp and I'm not quite sure why. Instead of 5:00pm as it is supposed to, but he str to time is reporting the correct 24 hour time. The unix timestamp is off, it keeps giving me the time as 12:00. I had some luck with this, but it doesn't format the time correctly. UPDATE 3 STR_TO_DATE(CONCAT('', endHour,':',endMinute, ' ', endTime),'%M %d %Y %h:%i%p') AS endingTime STR_TO_DATE(CONCAT(endHour,':',endMinute, ' ', endTime)) AS endingTime I was able to get some proper 24:00 time formatting using this, but still can't get it to format into Unix time. This is what pulls from the DB when using the concat SELECT CONCAT(startHour,':',startMinute, ' ', startTime) AS startingTime FROM events LIMIT 10 SELECT `id`, `tag`, CONCAT(startHour,':',startMinute, ' ', startTime) AS startingTime, CONCAT(endHour,':',endMinute, ' ', endTime) AS endingTimeĪlso having an issue with NULL, NULL times still get converted, event though the conversion isn't a correct time. INSERT INTO `tableName` (`id`, `label`, `startDate`, `endDate`, `startTime`, `endTime`) Here is the SUDO code (meaning I've changed table names and column names slightly). I also haven't been able to use that when dynamically inserting the concatenated rows into another db. I finally was able to get the columns to concatenate by using SELECT CONCAT(startHour,':',startMinute, ' ', startTime) AS startingTime FROM events īut I haven't been able to get the time to successfully go to unix time. I've googled and searched around the Internet and can't find a good set of documentation explaining how to do this. So the first issue I have is that I have to merge three columns into one column for over 6000 rows of data, while also converting it to UNIX time. The way that these were stored can be a little wonky from database to database (I was not the original author so I'm not sure of the reasoning for storing the information in the way that the person did, but I'm sure they had their reasons). So I'm working on migrating a database that has been created for keeping track of times and dates. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |