date problems because of mysql datetime/timestamp type damagery

[tibs]

wireRelativeTimeStr($page->published) returns weird results for me, i.e. "8 hours ago" for a page I just published on a site with +9:00 as its timezone (and -5:00 as the server / MySQL timezone, but I'm not sure if that matters.)

When I looked into the code, I saw that WireDateTime->relativeTimeStr() uses the numeric timestamp when that's what it's given; $page->published returns a unix timestamp, so that can't be the problem, right?

Then I checked the database, and the page table stores published in MySQL's datetime format (which lacks timezone support.) It seems that the application timezone setting was not taken into account somewhere during converting the database datetime field to the internal time representation (integer unix timestamp) in the PHP Page object.

"By the way," (and sorry to propose another change; people here will get tired of me so fast) wouldn't it make sense to switch to a simple integer fields, storing the unix timestamp, for the database? No more need to switch back and forth between the internal representation and MySQL's, which is sadly broken whenever timezones matter. And just, less work, right? (well, no more "current_timestamp" default value for modified, but I think there are workarounds for that  )

p.s. Why is "published" uses the datetime MySQL type while created and modified use timestamp? Timestamp (which is stored as UTC in the database) would probably make more sense for "published" as well (if only for consistency) -- that is, if we wanna stick to a native time column. As a note to this specific problem, looking up the "modified" field returned damaged results as well.

Edit: it's on pw 3

[tibs]

so the monstrosity that gives me the correct results is:

$mysqldiff = wire('db')->query('select timestampdiff(second, now(), utc_timestamp) diff')->fetch_assoc()['diff'];

$date = $item->published + date('Z') + $mysqldiff;

 

it seems like the configuration timezone is taken into account twice, and the database server's timezone never; but i'm kinda sleepy so maybe i shouldn't try to think about this

[tibs]

okay. it fixed it on my own server, but when i copied the whole thing on the other server (which is in a different time zone) things are off

which makes me really really wish we'd just use unix timestamps in the database

pretty please?

sorry, that last thing is actually doing the job on both servers; i should really go to sleep (it's a time zone thing)