Support

Home Forums Event Espresso Premium Calendar & Database Mismatch

Calendar & Database Mismatch

Posted: April 21, 2016 at 2:24 am


kellyjo

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:

Event Calendar:
https://goo.gl/Dmqq0R

In the backend of the website, when editing the event, the ID is 1010, and the event is set for 2016-04-21:
https://goo.gl/4rCsPK

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:
https://goo.gl/rc7RSV

Now I go into the database, in particular, the esp_datetime table, for EVT_ID 1010 and this is what I see:
https://goo.gl/CNq3s6

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.


Josh

  • Support Staff

April 21, 2016 at 12:10 pm

Hi there,

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:

https://github.com/eventespresso/event-espresso-core/blob/master/docs/F–Datetime-System/dates-times-timezones-in-models.md


kellyjo

April 21, 2016 at 2:31 pm

Hey Josh,

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.
Yet the database is showing April 22, 2016 or 2016-04-22.

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?


Tony

  • Support Staff

April 22, 2016 at 3:32 am

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,

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
Event end = 2016-04-21 10: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:

$datetime = EEM_Datetime::instance()->get_primary_datetime_for_event( $eventid, $try_to_exclude_expired = true, $try_to_exclude_deleted = true);

Then depending on exactly what you want to do you can do:

echo $datetime->get_i18n_datetime( 'DTT_EVT_start' );

or

$datetime->e_start_date()

To echo the values, or to store them:

$event_start = $datetime->get_i18n_datetime( 'DTT_EVT_start' );

or

$event_start = $datetime->start_date()

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.


kellyjo

April 22, 2016 at 5:27 pm

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.

Support forum for Event Espresso 3 and Event Espresso 4.
Documentation for EE3 and EE4
Documentation for Event Espresso 3

Documentation for Event Espresso 4

Status: closed

Updated by  kellyjo 3 years, 7 months ago ago

Topic Tags

Notifications

This topic is: resolved
Do NOT follow this link or you will be banned from the site!