Posted: April 21, 2016 at 2:24 am
There may be something weird going on with the database of Event Espresso 4.
I see the calendar as the correct date, and I see everything else as being correct, but when I check the database itself, where I’m trying to pull the information using an SQL query (SELECT DTT_EVT_start from wp_esp_datetime where EVT_ID = ‘”.$eventid.”‘), I am getting an entirely different date.
The event date is set for 2016-04-21 which is what it needs to be. But the database is showing 2016-04-22.
Here are some photos to explain the situation:
In the backend of the website, when editing the event, the ID is 1010, and the event is set for 2016-04-21:
When I go to the check-in page and view that event, I see this and it is the correct date as April 21, 2016:
Now I go into the database, in particular, the esp_datetime table, for EVT_ID 1010 and this is what I see:
The date is off by 1 day, yet its pulling in the correct date everywhere else, so what exactly is going on? I can’t seem to figure out why or how this is happening. There are several more dates in the calendar that are off, but it is not happening for every event. I do need this information to reflect all accurate dates for what I put into the calendar, as I am doing some custom work for a client.
Any help or insight would be appreciated.
It turns out the date times are recorded in the database as a mysql timestamp for the UTC+0 timezone, then when they’re displayed, they’re converted to your timezone set in WordPress > Settings > General.
There’s more information about date times in the developer documentation:
Thank you for the document, but it did not seem to help in this case, as I am not using any arrays or method objects from EE4, but rather just using a direct query to the database itself.
The timezone set in the WordPress settings is Denver to match MST where my client is located. If I am pulling directly from the database, than it is grabbing the exact DateTime that it shows in the database, not UTC, as the settings are similar to the calendar, but somehow appear to be an entire day ahead.
The SQL query was put inside of a functions.php plugin. I modified some code inside of a file called EE_Event_Registrations_List_Table.class.php inside the Caffeinated folder. My client wanted to add the Start Date at the end of every event from in the dropdown menu itself on the Check-in page.
I tried setting the timezone to America/Denver again, even though it was set. Why is it an entire day ahead? I could understand if I selected an England or further East time zone, but the timezone is Denver.
When all events were originally created, the time zone was America/Denver, so that rules out any timezones that are ahead of it that could have possibly caused it to get logged into the database as a day ahead of itself.
The Calendar Event date is April 21, 2016 or 2016-04-21.
No code was ever tampered with to change or update the database itself, only reading from it, so what else might be causing this issue?
Whilst it is grabbing the exact datetime from the DB, it is in UTC+0
Event Espresso stores ALL dates within the datebase in UTC+0 and then applies your current timeszones ‘offset’ (the time different between UTC and your timezone) when outputting the dates.
So looking at your last screenshot (direct dates/times from the DB) – https://goo.gl/CNq3s6
Event Start = 2016-04-22 00:30:00
Event End = 2016-04-22 02:30:00
Those are UTC+0 based timestamps meaning you need to apply the ‘offset’ for your current timezone.
Denver = UTC-6 (taking into account DST)
2016-04-22 00:30:00 – 6 hours = 2016-04-21 18:30:00
2016-04-22 02:30:00 – 6 hours = 2016-04-21 20:30:00
So when you apply the offsite of your current timezone to the UTC timestamps saved within the dates they show the correct dates/times:
Event start = 2016-04-21 08:30pm
As shown here: https://goo.gl/4rCsPK
Event Espresso does this for you when using the models, you can pull the primary datetime based on the event ID using:
Then depending on exactly what you want to do you can do:
To echo the values, or to store them:
Or use any of the other methods available on the EE_Datetime object to do this for you.
You can always install Kint and then wrap the $datetime variable within d(); to see what is available.
Josh and Tony, thank you for help!
Tony, your answer was exactly what I needed to make everything work!
Plugged it right in, worked like a charm for an exact date match!
Appreciate your help guys.
This topic has been resolved.
The support post ‘Calendar & Database Mismatch’ is closed to new replies.
Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.