Jump to content

Unix_timestamp off by 1 day


GANGSTERDAVE

Recommended Posts

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()),
 
 
 
 
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 |
+----+------------+
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.