Bug with dates/times

Timestamps are not generated by the plugin, they are provided by the database server. The plugin has no control over this.

The settings in the plugin about timezones only affect “date” type fields, where the user is inputting dates. The record timestamps are a different thing entirely.

I suggest you talk to your hosting provider about the configuration of your database server, there may be something they can do to fix this.

Hi there,

Thanks for getting back.

Could you give some specifics about what I should check with the hosting providers? I’ve tested this on two other separate reputable hosts, and can replicate similar results, so I am wondering what may you think may be at odds. All three are mainstream cPanel hosts.

And as with OP, no other plugins or WordPress behaviour is affected, which is why I suspect PD may be doing something odd in isolation.

And yes, the Record Timestamps are what I’m talking about. Did you watch my video demonstrating the issue?

Here is another video from a second separate host, replicating as above: https://archive.org/download/2022-02-06-11-23-01/2022-02-06%2011-23-01.mp4

At ~0:05, note the local time is 11:22:56am. Timezone is set to Melbourne. Correct.
At ~0:26, when adding a participant, note the record time stamp on creation is 12:23am, which is incorrect.
At ~1:00, the record time on viewing a record (after it has been saved) has changed from above to 11:23am, and is correct.
At ~1:10, note the record time when viewing the list is 10:23pm which is incorrect.

Here’s also a screenshot of what timestamp Participants Database has written into its table. The values look correct there:

https://i.postimg.cc/gYqZ2222/Screenshot-2022-02-06-at-11-29-22.png

With respect, by process of elimination, something in Participants Database seems broken to me.

If you look the query that is used to write a record to the database, you’ll see that for the timestamp, it sends “NOW()” as the timestamp value. The plugin does not change the configuration of the database server, so how it assigns the value of NOW() depends on how the server is configured.

Other plugins may be setting the timestamps with a specific value, so that could account for the difference.

If there is a bug, I would be happy to fix it, but as far as I can tell, there isn’t anything wrong with how the plugin is interacting with the database server.

I also don’t see this problem in my tests here. Our database server is set to UTC (which is standard) and the timestamps I am seeing in my tests are correct.

I now realize now that you’re talking about the time as displayed in the list (i was looking at the db entry directly), which is affected by the WP timezone.

Did you try it with “Sync php Timezone” unchecked?

In my tests, I got the timezone as UTC with that checked, with it unchecked, I got my local time (UTC-10).

• This reply was modified 2 years, 9 months ago by xnau webdesign.

Please check my submission materials carefully and actually watch the screen recordings.

You can see in the OP video that yes, I have toggled “Sync php Timezone” setting on and off and the behaviour persists regardless.

And in the supplementary video, I’ve made notes about how/where you can see the time changing between screens:

At ~0:05, note the local time is 11:22:56am. Timezone is set to Melbourne. Correct.
At ~0:26, when adding a participant, note the record time stamp on creation is 12:23am, which is incorrect.
At ~1:00, the record time on viewing a record (after it has been saved) has changed from above to 11:23am, and is correct.
At ~1:10, note the record time when viewing the list is 10:23pm which is incorrect.

OK, I see that the plugin is inconsistent about how it applies the timezone.

What you’re seeing on the admin Add/Edit Participant page is the timestamp that is in the record without any timezone correction. This is usually UTC, but it could be different depending on your database server config. If the db server timezone is not UTC, that might throw things off…I have never tested a setup like that.

On the admin List Participants page, the timestamp column shows the time adjusted for the timezone. This is also the case with the frontend list if the timestamp is included in the columns.

In my tests, the timezone correction is correct when it is applied.

Thanks for getting back.

OK, I see that the plugin is inconsistent about how it applies the timezone.

Great, thanks.

If the db server timezone is not UTC, that might throw things off…I have never tested a setup like that.

Okay, to assist then, here is a rundown on the setups for the three different servers/hosting accounts I tested and had the inconsistencies:

First server, European host, web server timezone is IST, mysql database (mariaDB) using “SYSTEM” timezone and now(); matches; the default system/account setting for PHP appears to use UTC:

Second server, host was Greengeeks in United States, server in Amsterdam, web server timezone is CET, mariaDB using “SYSTEM”, host allowed changing PHP.ini setting to Melbourne:

Third server, host and datacentre is in our country (still cPanel shared host though), timezone AEDT, database also same, but host does not allow PHP.ini so PHP defaults to UTC:

So, how can we tackle this on shared hosts? The webserver timezone setting cannot be changed, maybe DB timezone could be (but I’m not sure, and would probably also vary host to host) and the PHP.ini settings sometimes can or cannot be changed for the whole account, also depending on the host.

I’m not a developer/not at all great at coding, but my first thought is, would it make more sense for Participants Database to use the WordPress current_datetime() (introduced in 5.3) instead of now() to do all the database writes? That way you avoid the potential differences between webserver/mysql time and PHP time, AND also capture WordPress timezone setting?

… or wp_date();

Until now, I have not considered changing the use of the NOW() value for setting a timestamp. This is the only report of a problem I have seen with this, the plugin has been doing it this way since the beginning, so this is why I suspected it was something particular to your configuration. This is not an area of expertise for me, so I can’t comment on the details you provided, nor do I know how to solve the issue you’re seeing. Without an indication that this is an issue affecting the plugin’s user base in general, I’m reluctant to change it.

Fair enough.

But the problem outlined above is: since you say you’re using now() to write the times instead of something that goes through WordPress’s settings (like wp_date() for example) this bug will persist in setups like mine (and many other shared hosts) where the date settings vary between the webserver, mysql sever, and the default PHP settings—and these cannot be easily changed (if at all) on shared hosts (which is why I tested three different servers with different hosting companies and could replicate the result on each).

This is why I suggest using the settings that WordPress applies to PHP (i.e. the WordPress timezone setting) and a function like wp_date() to do writes that captures this setting. I can foresee this would solve the problem of mysql writing its now() time which may be different to the PHP timezone (as illustrated above), and may also solve any time shifting issues you may have had in the past with other setups also diverging from UTC—because the times PD will *always* be writing a time that is in sync with the WordPress timezone setting. This solves the problem of having to rely on the system defaults (which, as above, can vary wildly and have nothing to do with the WordPress settings).

Judging by your post mentioned in the OP, and the comments on that page, it seems like you’ve had a few other people mentioning timezone quirks in the past, so I think I’m surely not the only one. Perhaps the difference here is that I’ve provided a more comprehensive bug report than simply “it doesn’t work.”

Anyway, long story short, I hope you’d consider at least experimenting with a change, otherwise we might have to resort to getting the plugin hacked to fix this bug, which is unwanted cos it will wipe us out on every update…

I think this is a great plugin and want to help it succeed!

So, to help convince you, and allay your concern, I imagine the change would be transparent for those *not* facing issues with now() because the timezone setting would be unchanged in that use-case, and I also don’t think it’s a dangerous change to move from now() to something like wp_date() in any event—the WordPress time/date functions are well documented and something like wp_date() was specifically designed and deployed in 5.3 to solve many of the legacy issues with locale quirks like this.

TLDR; I hope you consider at least testing some changes!

If I can be of any help, please let me know. We’d love to use this plugin in our grassroots community group, but the record dates need to be correct for us for book keeping reasons.

Thanks!

• This reply was modified 2 years, 9 months ago by Trippetchz.

Thanks for your thoughtful input, date/time issues are tricky for all the reasons you cite, and I appreciate the help in looking at the issues.

I am revisiting this in an effort to standardize the way the plugin displays dates, and hopefully, this will work better for cases such as yours.

PS: Also, hopefully with something like wp_date() you won’t have to reinvent the wheel, as it looks like in participants-database\classes\PDb_List_Query.php and participants-database\classes\PDb_admin_list\query.php with all the hardcoded calculating a difference from UTC and Linux epoch—which is precisely the problem here: what happens if UTC is never used or FROM_UNIXTIME is wrong (because again, like above, these are hard low-level server settings that cannot be changed on a shared host)?

I hope I’ve made the case that using a WordPress function to write dates/times will be much easier for you in the long run, less buggy, less complex, and more flexible for other people’s timezones.

??

Thanks for the consideration ??

Hello there,

I see you have made some changes in 2.0.1 to begin ironing this out. Thanks!

Unfortunately, the bug isn’t completely squashed, although behaviour with ‘Date Recorded’ seems to be better.

Listing members in the admin “Date Recorded” times is now correct, but “Date Updated” isn’t, both on individual records, and in the list. Here are the screenshots to illustrate what I mean:

The “Date Updated” time should match the “Date Recorded” in above example as it was both created and saved at the same time.

And for an example of an edit, my local time (Australia/Melbourne) is 13 March 2022 7:03pm but the field shows 13 March 2022 8:03 am

• This reply was modified 2 years, 8 months ago by Trippetchz. Reason: typo
• This reply was modified 2 years, 8 months ago by Trippetchz. Reason: context

When a record is created, either in the backend or when the signup form is submitted, the same value is placed in both the Date Recorded and Date Updated columns. The only reason they would be different is if the record was edited after that: the Date Updated value will be updated at that time.