Latest version 4.9

Bloke · 2026-06-12 17:56:40

etc wrote #343351:

Just in case, what SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1)) returns?

I get 1 out.

etc · 2026-06-12 21:51:30

Anyone could send me credentials for a faulty txp site? Or, better, make a sandbox site, to debug safely.

Bloke · 2026-06-12 22:45:58

etc wrote #343354:

Anyone could send me credentials for a faulty txp site?

You have mail.

abu · 2026-06-13 11:22:04

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’

jakob · 2026-06-13 15:08:32

Bloke wrote #343352:

I get 1 out.

I do too on the aforementioned server.

etc · 2026-06-15 19:21:25

I thinks the best we can is to revert 4.9.2 to unix epoch (1970 – 2038) and extend it in a next version, maybe even txp 5. There is too much weirdness around.

Bloke · 2026-06-16 08:20:00

etc wrote #343360:

I thinks the best we can is to revert 4.9.2 to unix epoch (1970 – 2038) and extend it in a next version.

Boooooooooo. I really like the fact that dates can extend – almost – back as far as 0. It’s a nice product feature, especially for historical blogs.

Are we fairly certain this is a MariaDB oddity? i.e. MySQL behaves – even v8 and v9? Or does it affect both? And is it solely the changes made in 4.9.x to extend the date range that caused it? Or would it have occurred anyway because of changes made in MariaDB? Or is it a long-standing Txp bug?

At this very moment of writing, I have this situation:

Me at the laptop in the UK: 09:20am.
My Txp site on a server in Germany: 10:20am.
UTC: 08:20am.

As far as I see things, we have two issues:

The MariaDB zero-time bug that makes things 1-second out, or throws errors if a 0-value datestamp is used.
The off-by-one-hour-in-the-future issue when publishing content.

For the latter, is it only off by one hour because of the time difference between “me” and “my server”? Or “me” and “UTC”? i.e. if my server was in San Francisco, would my new article be posted 8 hours earlier than “me” time? And if it was in Singapore, my article wouldn’t show up on the site for another 6 hours?

Maybe if we can answer that, we might be able to fix the posted date by adding either the server timezone offset (which I thought we did anyway in safe_strftime()) or allowing for the UTC<->Me time difference?

etc · 2026-06-16 16:33:39

Okay, the quirk seems to be caused by ‘Reset timestamp to now’ checkbox. If I uncheck it when publishing a new article, the latter is live immediately. Investigating.

Bloke · 2026-06-16 16:40:31

etc wrote #343365:

Okay, the quirk seems to be caused by ‘Reset timestamp to now’ checkbox. If I uncheck it when publishing a new article, the latter is live immediately. Investigating.

Intriguing. Good sleuthing!

phiw13 · Yesterday 07:29:25

etc wrote #343360:

I thinks the best we can is to revert 4.9.2 to unix epoch (1970 – 2038) and extend it in a next version, maybe even txp 5. There is too much weirdness around.

Raise hand… I use this feature to set image dates to the 1920~1925 range, something like 130 images. Love it.

–^–

I have no issues with those date / time oddities as mentioned in this thread with MySQL 8.4.7 and Percona 8.4.8 equiv. Two different servers, One in HongKong, one Singapore. Both have the SQL server set to UTC, there is a one hour time zone difference between me and the servers, TXP is set to use the Japan Time .

etc · Today 07:43:16

Bloke wrote #343350:

A further random thought: does this issue show up only in summer when DST is in effect, and disappear in winter?

You are right, it looks like TIMESTAMPDIFF() and UNIX_TIMESTAMP() do not handle DST the same way, whatever sql version. Side-effects of winter coding..

phiw13 · Today 07:59:13

etc wrote #343372:

You are right, it looks like TIMESTAMPDIFF() and UNIX_TIMESTAMP() do not handle DST the same way, whatever sql version. Side-effects of winter coding..

Ah, that might explain why I don’t see this date issue. I never set or use DST.

The “Set timestamp to now” issue mentioned above does seem the affect the issue I see here.

Bloke · Today 15:11:15

etc wrote #343372:

You are right, it looks like TIMESTAMPDIFF() and UNIX_TIMESTAMP() do not handle DST the same way, whatever sql version.

Wow, that is intriguing. So do we need to standardise on one in the code? Or simply be cognisant of which one is being used and adjust accordingly?

etc · Today 15:29:32

It is just me being silly. We use e.g.

SELECT TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), "2026-03-29 03:00:00")

as substitute for UNIX_TIMESTAMP("2026-03-29 03:00:00"). The problem is, TIMESTAMPDIFF() merely subtracts two dates, without taking DST into account, as if they were UTC. For example

TIMESTAMPDIFF(SECOND, "2026-03-29 02:00:00", "2026-03-29 03:00:00") = 3600

But in reality (Northern hemisphere) this two dates are equal (due to DST switch), so the difference should be zero.

It only remains to find a cheap solution (not using CONVERT_TZ() or other expensive functions on each row). Progress!

Bloke · Today 18:14:31

Ahhh okay. Yes that makes sense now you state it like that. And it would mean we should adhere to the standard timezone operating rule

The rule is simple: store in UTC, calculate in UTC, convert to local only at the display layer.

I suspect the reason we get tied in knots is because we don’t store our timestamps in UTC (at least I don’t think we do. I vaguely remember some function taking the server timezone into account before storing, but that may just have been a fever dream).

If we don’t already store them in UTC, I presume there’s no sane way – given a server timezone and/or local timezone – to romp through a database and bulk convert all stored timestamps to UTC so we can do all maths on UTC dates and make this problem go away?

Textpattern CMS support forum

#31 2026-06-12 17:56:40

Re: Latest version 4.9

etc wrote #343351:

#32 2026-06-12 21:51:30

Re: Latest version 4.9

#33 2026-06-12 22:45:58

Re: Latest version 4.9

etc wrote #343354:

#34 2026-06-13 11:22:04

Re: Latest version 4.9

#35 2026-06-13 15:08:32

Re: Latest version 4.9

Bloke wrote #343352:

#36 2026-06-15 19:21:25

Re: Latest version 4.9

#37 2026-06-16 08:20:00

Re: Latest version 4.9

etc wrote #343360:

#38 2026-06-16 16:33:39

Re: Latest version 4.9

#39 2026-06-16 16:40:31

Re: Latest version 4.9

etc wrote #343365:

#40 Yesterday 07:29:25

Re: Latest version 4.9

etc wrote #343360:

#41 Today 07:43:16

Re: Latest version 4.9

Bloke wrote #343350:

#42 Today 07:59:13

Re: Latest version 4.9

etc wrote #343372:

#43 Today 15:11:15

Re: Latest version 4.9

etc wrote #343372:

#44 Today 15:29:32

Re: Latest version 4.9

#45 Today 18:14:31

Re: Latest version 4.9

Board footer