Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Re: Latest version 4.9
A further thought: the Timezone Pref instructions are, I think, unclear. The way it’s worded implies it’s the user’s timezone that needs to be chosen. But (if I’m reading the code right) it should actually be the timezone of the server.
The user’s timezone (the person using the admin side to create content) and the visitors timezone (viewing the site) could be anywhere in the world and depends on what the browser tells us is their timezone.
General guidelines state that handling dates correctly, requires:
- Storing them in UTC.
- Doing calculations in UTC.
- Applying the server offset when displaying dates.
However. Things get complicated when someone’s setting an article datestamp to be an event. E.g one that starts on 30 Nov 2026 at 15:00. If they set the article to that datetime and hit Publish, the server offset will be applied. And, a secondary problem is that, by the time the event begins, DST will not be in effect, but it is in effect today (in the UK at least) when the article is published.
Burdening the user to know the server’s timezone offset when creating article dates, and taking into account whether DST will be in effect at the time the event occurs, and whether it differs from DST at the time the article is Saved, is quite an ask.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Hire Txp Builders – finely-crafted code, design and Txp
Offline
Re: Latest version 4.9
UTC storage would be fine for our purposes if CONVERT_TZ() (or some php equivalent) worked well outside of the unix epoch, but it does not (currently) seem so. I guess it internally depends on UNIX_TIMESTAMP(), but if the latter worked well, our problem would be solved anyway (by reverting to pre-4.9 code). Actually, recent mysql/mariadb/etc servers can handle the post-2038 dates (on 64-bits systems), but not the pre-1970 ones.
What we could do in 4.9.2 is patching the dst issue for the unix epoch dates and not care about 1 hour gap for the other ones.
Offline
Re: Latest version 4.9
etc wrote #343380:
What we could do in 4.9.2 is patching the dst issue for the unix epoch dates and not care about 1 hour gap for the other ones.
This conversation is a bit over my head, so I may be misunderstanding this last sentence. It is definitely important that posting an article in the moment makes it live in the moment and not an hour later. Sorry, that’s probably stating the obvious.
TXP Builders – finely-crafted code, design and txp
Offline
Re: Latest version 4.9
jakob wrote #343381:
It is definitely important that posting an article in the moment makes it live in the moment and not an hour later.
Yes, that’s what I mean, but we would guarantee it only until 2038. An eventual one hour gap for other publish dates (beyond 1970 – 2038) does not look critical, and I hope the issue will be solved by mysql/mariadb/etc maintainers before 2038.
Offline
Re: Latest version 4.9
abu wrote #343356:
I stumbled upon another issue relating to the COALESCE/FROM_UNIXTIME trickery. On updating an article, the code
$whenposted = "COALESCE(FROM_UNIXTIME(0), FROM_UNIXTIME(1)) + INTERVAL $uPosted SECOND";throws an error.
ERROR 1292 (22007): Truncated incorrect unixtime value: ‘0.0’
Hopefully, this fixes it, otherwise I have no clue.
Offline
#51 Today 06:44:48
Re: Latest version 4.9
etc wrote #343384:
Hopefully, this fixes it, otherwise I have no clue.
Nope, but thanks for trying to help. The variable $uPosted isn’t the issue at all. The key is moving the COALESCE calls out of the query string.
Anyway, I don’t get the point of inserting a function that results in a constant value in each query string and evaluating it over and over again.
For fixing the updating issue, I appended a new constant at the end of the file txplib_db.php. There might be better places to put that code, but with my limited knowledge of the Textpattern universe… 😉
global $unixtime0;
if (!defined('UNIXTIME0')) {
if (getThing('SELECT FROM_UNIXTIME(0)')) {
define('UNIXTIME0', 'FROM_UNIXTIME(0)');
} else {
define('UNIXTIME0', 'FROM_UNIXTIME(1)');
}
$unixtime0 = getThing('SELECT ' . UNIXTIME0);
}In the file txp_article.php I’ve applied the following changes. The content of $whenposted from
$whenposted = "COALESCE(FROM_UNIXTIME(0), FROM_UNIXTIME(1)) + INTERVAL $uPosted SECOND";to
$whenposted = "'$unixtime0' + INTERVAL $uPosted SECOND";The same applies for the content of $whenexpires.
I expect that all invocations of COALESCE could be eliminated similarly.
Offline
#52 Today 11:27:24
Re: Latest version 4.9
Thanks for testing and the constant suggestion. I do not think it really improves the performance (the optimiser should cache FROM_UNIXTIME(0) anyway, and COALESCE() is very cheap), but will implement it. Just can not grasp how COALESCE() can be an issue here.. what happens in sql world?
Offline
#53 Today 12:20:05
Re: Latest version 4.9
Hey guys, how do oter open source projects handle this?
I’m sure that there is a stable solution out there.
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline
#54 Today 12:21:57
Re: Latest version 4.9
etc wrote #343391:
the optimiser should cache
FROM_UNIXTIME(0)anyway, andCOALESCE()is very cheap
Even if caching might reduce the impact of this massive code duplication, it’s also a matter of code readability.
Offline
#55 Today 12:47:48
Offline