Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Re: List all articles for a dynamic time period: limit 1 year?
@etc, you are a genius. I think this solves the problem! I’ll have the site’s managing editor look at it too, just in case I’m missing something, and I’ll report back if I get confused. Thank you again!
Offline
Re: List all articles for a dynamic time period: limit 1 year?
Bloke wrote #280023:
Jukka has a raft of timezone issues assigned to fix. I’ve unionised date generation in PHP to rid us of the blessed MySQL now() problem as part of the custom field improvements.
The select or generation? In selects you can use the lastmod string we are storing inside preferences table (this improves caching ability), and the generation must be based on DateTime, so that you can define the timezone you are using when creating dates. E.g.
class Textpattern_Date_Timestamp extends DateTime
{
/**
* {@inheritdoc}
*/
public function __construct($date = 'now', $timezone = null)
{
if ($timezone === null) {
$timezone = new DateTimeZone('UTC');
}
parent::__construct($date, $timezone);
}
public function __toString()
{
return (string) $this->format('Y-m-d H:i:s');
}
// ...
}You also should set the server’s global timezone to UTC (to get rid of warnings) and handle Textpattern’s timezone setting locally with a DateTime class rather than with the current weird offset based calculations. E.g.
class Textpattern_Date_Format extends Textpattern_Date_Timestamp
{
/**
* {@inheritdoc}
*/
public function __construct($date = 'now', $timezone = null)
{
parent::__construct($date, $timezone); // Date is from database and as such is in UTC.
$this->setTimezone(new DateTimeZone(get_pref('timezone'))); // Add site's timezone to the date.
}
}Converting user given date to UTC (for database), is the opposite of that. The given $timezone is get_pref('timezone') and the set timezone (converted target) is UTC.
The current functions safe_strftime() and safe_strtotime() should be marked deprecated, and the whole auto_dst, is_dst and gmtoffset removed, leaving just the timezone setting to the database.
If someone can just figure out how to preserve the existing article timestamps while converting the database dates to a more universal representation to sidestep this problem altogether, that’d be terrific.
The best you can do is to subtract the timezone difference from the current MySQL server timezone to UTC, giving us stable UTC based starting point. This of course won’t fix the issue that the dates are already flawed and mixed back of dates in PHP timezone, UTC, MySQL timezone and PHP application server’s timezone, and PHP global userland timezone.
Last edited by Gocom (2014-04-04 07:46:11)
Offline
Re: List all articles for a dynamic time period: limit 1 year?
Gocom wrote #280057:
The select or generation?
For now while I’m testing, it’s just a single global $txpnow = time() which means every select or update uses the same timestamp to generate the dates MySQL needs. That just prevents the ‘drift’ of one or two seconds that we had before as the script executes (it could potentially screw up custom field display because of the fact fields can expire or be created at certain dates).
Longer term, once I’ve proven the custom fields thing works OK, we’ll hook into whatever DateTime class you dream up to resolve Issues 401-403 :-)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: List all articles for a dynamic time period: limit 1 year?
johnstephens wrote #280050:
I’ll have the site’s managing editor look at it too, just in case I’m missing something, and I’ll report back if I get confused.
In case you disable DST (or don’t care about 1 hour glitches), the following query should run noticeably faster on large datasets:
SELECT ID FROM textpattern WHERE Status=4 AND Posted BETWEEN CAST('{?archive_year||-1}-12-31 21:00:00' AS DATETIME) AND CAST('{?archive_year}-12-31 20:59:59' AS DATETIME)Offline