GANGSTERDAVE Posted March 17, 2015 Share Posted March 17, 2015 Hi, We have a script for uploading podcasts. Unfortunately, everything I upload is posted with the date 1 day behind. The date is correct when uploading, but when I view the upload it is, as said, 1 day behind. I've spent the entire day working on this, no luck. Any advice would be greatly appreciated. I believe the last two lines of the following (date and date_added) is where the problem lies. Thanks. $title = $_POST['title']; $subtitle = addslashes($_POST['subtitle']); if($_POST[delete]=="delete"){ $query = "DELETE from service where id = $_POST[id] LIMIT 1"; }else{ $query="UPDATE service SET title = '$title', series_id = '$_POST[title]', service = '$_POST[service]', speaker = '$_POST[speaker]', date = UNIX_TIMESTAMP($_POST[date]), date_added = UNIX_TIMESTAMP(NOW()), Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 17, 2015 Share Posted March 17, 2015 Hmm, my understanding is that MySQL will automatically store the UNIX timestamp based upon the current timezone that the MySQL server is configured for. So, there should be no reason to use UNIX_TIMESTAMP() in the query. However, it is also my understanding, that MySQL will return the time also based on the current timezone. So, if I store the time of 14:00 (2PM) on a server configured in the Central Timezone (UTC - 6), the time is stored as 20:00 (8PM). But, when I return the value it will still be displayed as 14:00 because the default setting for the server is Central Time. But, I could change the locale settings before running a query to have the time returned based on any timezone I wish. That is how DBs handle dates in order to easily support multiple timezones. By using UNIX_TIMESTAMP() I think you are 'corrupting' the time. MySQL is expecting a time to be provided in the current timezone. So, in my example above, if UNIX_TIMESTAMP() was used on the value before it was saved it would modify the timestamp to 20:00 (8PM). Then, since MySQL is expecting that the timestamp is in Central time, it modifies it upon saving to 02:00 (2AM) - the next day. It would still return 8PM when retrieved since it thinks that's the time in the current timezone. But, based on what the server's timezone is set for and the time of day you are running your tests, this could explain the discrepancy. So, save the values without UNIX_TIMESTAMP(). If you need that value, then use it on the values when retrieving them. Quote Link to comment Share on other sites More sharing options...
GANGSTERDAVE Posted March 17, 2015 Author Share Posted March 17, 2015 THANK YOU! I will try this and report back. Quote Link to comment Share on other sites More sharing options...
GANGSTERDAVE Posted March 17, 2015 Author Share Posted March 17, 2015 Sorry to be such a noob but I can't seem to find anything to replace "unix_timestamp", such as LOCALTIME etc. thanks you for your help Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 17, 2015 Share Posted March 17, 2015 you should be storing your dates/datetimes as a mysql DATE or DATETIME data type (unix timestamps are an abomination leftover from the 1970's). if the incoming data isn't already formatted correctly, you can either format it in your php code or use the mysql STR_TO_DATE() function in a query when you store/insert it. what is an example of the incoming $_POST['date'] value? Quote Link to comment Share on other sites More sharing options...
GANGSTERDAVE Posted March 17, 2015 Author Share Posted March 17, 2015 (edited) Its a drop down box and its formatted as 20150315 for March 15th 2015 that is, the date is selected from a drop down (calendar) box, and inserted into the form as 20150315 Edited March 17, 2015 by GANGSTERDAVE Quote Link to comment Share on other sites More sharing options...
Barand Posted March 17, 2015 Share Posted March 17, 2015 That date value as it is will insert into a DATE type field; CREATE TABLE `test_date` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO test_date (date) VALUES (20150315); SELECT * FROM test_date; +----+------------+ | id | date | +----+------------+ | 1 | 2015-03-15 | +----+------------+ Quote Link to comment Share on other sites More sharing options...
GANGSTERDAVE Posted March 17, 2015 Author Share Posted March 17, 2015 ok, thank you so much everyone. I think the solution is going to be creating new datetime fields, populating them, then changing the php file to reflect the new. Will update this thread soon. Any other advice from anyone very welcome. Thanks again everyone Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 17, 2015 Share Posted March 17, 2015 You should also work on using proper array notation..A reference to $array[index] causes unnecessary work for the php interpreter as it searches for the correct element in the array. It usually works but can fail if your index name matches some other declared constant value. Proper array syntax if $array['index']. Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 17, 2015 Share Posted March 17, 2015 (edited) You should also work on using proper array notation..A reference to $array[index] causes unnecessary work for the php interpreter as it searches for the correct element in the array. It usually works but can fail if your index name matches some other declared constant value. Proper array syntax if $array['index']. No, his syntax is valid. He only omitted the quotes around the array index when used within a double-quoted string. The below is strait from the manual. But, I personally never use arrays within quoted strings without using the curly brace (in which case the quotes around the array key are necessary). // The following is okay, as it's inside a string. Constants are not looked for// within strings, so no E_NOTICE occurs here print "Hello $arr[fruit]"; // Hello apple Edited March 17, 2015 by Psycho Quote Link to comment Share on other sites More sharing options...
Barand Posted March 17, 2015 Share Posted March 17, 2015 (edited) You should also work on using proper array notation..A reference to $array[index] causes unnecessary work for the php interpreter as it searches for the correct element in the array. It usually works but can fail if your index name matches some other declared constant value. Proper array syntax if $array['index']. He has used the proper array notation. Outside quoted strings he uses $_POST['title']. Inside quoted strings the index quotes are unnecessary as it cannot be taken for a defined constant as this example shows error_reporting(-1); define('index', 42); $data = array( 'index' => 123, 42 => 'constant used' ); echo "$data[index]"; // --> 123 echo $data['index']; // --> 123 echo $data[index]; // --> constant used [EDIT] Beaten at the post Edited March 17, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 17, 2015 Share Posted March 17, 2015 Another day of learning for me! Thank you for the info. 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.