Heads up!

Hi guys, we see a lot of people struggling with the recent TIME ZONE CHANGE in the United States, and with the upcoming time zone change in Europe (March 26th). If you see incorrect times in the appointments booked after Daylight Savings Time starts, the time zone of your WordPress site needs to be adjusted.

Please check out THIS ARTICLE about the time zones in Amelia.

Okay
  Public Ticket #1295752
MYSQL Query Interval
Closed

Comments

  • MattHadfield113 started the conversation
    September 7, 2017 at 9:45am

    Hello,

    I have a query that i am trying to run (See Below), however the query comes back with an error -

    Error

    unknown [expr_type] = reserved in "WHERE" [4]


    The query i am executing is -

    SELECT DATE(`created`) AS created_date, `Extension`, SUM(CASE WHEN `Call_type` = "Incoming" AND `Extension` != 2504 THEN 1 ELSE 0 END) AS CallsAnswered, SUM(CASE WHEN `Call_type` = "Incoming" AND `Extension` = 2504 AND `Dialed_digits` LIKE 'R%' THEN 1 ELSE 0 END) AS CallsRecieved FROM mp_wp_call_report_pstn WHERE `created` BETWEEN NOW() - INTERVAL 31 DAY AND NOW() GROUP BY DATE(`created`) ORDER BY `created` ASC LIMIT 0, 32

    Removing the "INTERVAL 31 DAY" resolves the issue and allows the query to proceed. However when processing this manually on MYSQL, the query works without issue and provides the relevant data.

    Is there a way you have to specify an interval or is this not supported in wpDataTables ?

    Regards,

    Matt

  •  1,362
    Miloš replied
    September 7, 2017 at 11:31am

    Hi Matt,
    Thank you for your purchase.

    Our recommendation when creating mysql tables is not to use functions like SUM, GROUP BY, ORDER BY etc.
    If you have some complicated query my suggestion is to make a VIEW in phpmyadmin or any other database management system and see if that query returns the desired result.
    If the result is good , use that query as an input when creating the tables with Add from data source option.

    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

    ​