Jump to content

date problems because of mysql datetime/timestamp type damagery

Recommended Posts

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 :rolleyes: )

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

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

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)

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Atlasfreeman
      So im doing a website. and i put on multi language on the website and uploaded some new images when i decide to make a new page...
      This i can't do anymore...

      It sais : 
      Add New
      The process returned no content.
      Unknown template.

      Well the website is showing fine, but i can't make new pages 😞

      Do any have any idea what to do?
    • By Elchin
      I want select pages where now between date and end_date or now bigger than date and end_date is empty.
      I have five tried variants:
      $start = strtotime(date('Y-m-d') . " 00:00:00"); $results = $page->children("foo=(date<$start,date_end=''),bar=(date<$start,date_end>=$start),sort=-date,limit=12"); $start = strtotime(date('Y-m-d') . " 00:00:00"); $results = $page->children("date<$start,(date_end='',date_end>=$start),sort=-date,limit=12"); $start = strtotime(date('Y-m-d') . " 00:00:00"); $results = $page->children("date_end=''|date_end>=$start,date<$start,sort=-date,limit=12"); $start = strtotime(date('Y-m-d') . " 00:00:00"); $results = $page->children("!date_end|date_end>=$start,date<$start,sort=-date,limit=12"); $start = strtotime(date('Y-m-d') . " 00:00:00"); $results = $page->children("date_end>=$start|!date_end,date<$start,sort=-date,limit=12"); All this variants not worked for me and returned zero results.
    • By MateThemes
      Hello everyone.
      I have a question that i can't find a way to solve.
      I have following function in _uikit.php 
      $date = $page->get('date|createdStr'); $dateModified = $page->get('datemodified'); But I need to output the $date in to different formats.
      My further function looks like this
      // return the blog post article markup return " <div> <article class='uk-article blog-post $class'> <meta property='name' content='$page->title'> <meta property='author' typeof='Person' content='Arra Lifte Harmanschlag'> <meta property='dateModified' content='$dateModified'> <meta property='datePublished' content='$date'> <meta class='uk-margin-remove-adjacent' property='articleSection' content='News'> <div property='image' typeof='ImageObject'> $featuredBlogPostImage </div> $heading <ul class='mt25 uk-margin-remove-bottom uk-subnav uk-subnav-divider'> <li class='uk-article-meta'> <time datetime='$date'>$byline</time> </li> </ul> <div class='mt25' property='text'> $body </div> </article> </div> "; Now I need to output the meta property in this format 2019-03-02CET05:23:00 and then a normal date format that is displayed on the Homepage with 2. März 2019 without time.
      Can anybody help me?
      Thanks in advance.
    • By Guy Incognito
      I added some custom styles to the CKeditor menu bar using the example mystyles.js and the PW tutorial. This worked fine for fields when editing on the frontend. But none of our custom styles showed in the backend editor dropdown unless we edited the core copy of mystyles.js in wire/modules.
      Is this correct behaviour, a bug or a mistake on my part? Tried clearing cache, logging in/out etc but the backend ignores our custom styles in the site/modules path.
    • By karian
      I don't know why multiple instances (repeater_repeat_columns1, repeater_repeat_columns2, ...) of my repeater field are displayed inside Template field (see image).
      Is there a way to clean/reset it ?

  • Create New...