Unix_timestamp off by 1 day

[GANGSTERDAVE]

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()),

 

 

 

 

[Psycho]

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.

[GANGSTERDAVE]

THANK YOU! I will try this and report back.

[GANGSTERDAVE]

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

[mac_gyver]

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?

[GANGSTERDAVE]

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

[Barand]

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 |
+----+------------+

[GANGSTERDAVE]

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 

[ginerjm]

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'].

[Psycho]

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

[Barand]

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

[ginerjm]

Another day of learning for me! Thank you for the info.