How to add CONVERT_TZ in Module Prefilter

[keianrad]

I need to return all past records from current day then I use the code listed below in a query out of fabrikar that it works. then I need to return same result by a fabrikar menu item, but I don't know what should I add in filter WHERE clause. I tried with different things but the didn't work.

(CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") >= CURDATE() - INTERVAL 5 YEAR AND CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") < CURDATE())

Thank you for your help.

 

[Sophist]

1. Goto Fabrik options and turn on Fabrik debugging.

2. Go to the page, then add "?fabrikdebug=1" or "&fabrikdebug" to the URL to get Fabrik's debugging info. Somewhere in the accordion that will be output will be the SQL that is created.

You should be able to compare that SQL with SQL that works outside of Fabrik and see what needs fixing.

If you need more help, please post details of the SQL that works, your current WHERE clause and the SQL SELECT output from fabrikdebug and I will take a further look.

 

[cheesegrits]

past records from current day

Just so I'm sure I understand, you are trying to get records from the past 5 years (so between "today - 5 years" and "today")?

And, when you say "current day", do you mean "relative to time now", or "same day"? The query example you gave

(CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") >= CURDATE() - INTERVAL 5 YEAR AND CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") < CURDATE())

... would include the time, so say the current time is 3pm, it'll show you due dates <= 3pm today.

Also, in that query, you aren't doing any TZ conversion, as you are specifying 0 from both zones.

If you don't want to include the time part, so just want to show tasks due "today" (so the date is today, ignoring the time part), use the DATE() function to extract just the date part of the due_date, so the WHERE clause would be (removing the TZ conversion for now):

    (      
            DATE(app_tasks.due_date) >= CURDATE() - INTERVAL 5 YEAR
        AND
            DATE(app_tasks.due_date) <= CURDATE()
    )

... or use the BETWEEN operator ...

DATE(app_tasks.due_date) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()

In order to do a prefilter which can't be built with the GUI (which this can't, if you are needing to extract just the DATE(), you have to do a subquery, like this:

Field: id
Condition: IN
Value: SELECT id FROM app_tasks WHERE DATE(app_tasks.due_date) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()
Type: query

So the "value" becomes a subquery that returns a result set of app_task ids that match the WHERE.

If you do need to do the TZ conversion, add it like this:

Value: SELECT id FROM app_tasks WHERE DATE(CONVERT_TZ(due_date, "+0:00", "+5:00")) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()

... which would add 5 hours to the datetime before extracting the date part. Which direction you need to modify your dates in (if at all) depends on whether you are storing the date in local of GMT, and what time zone your MySQL thinks it is in. Trial and error should tell you.

-- hugh

 

[Sophist]

A much better answer than mine!!

 

[cheesegrits]

PS, for documentation on CONVERT_TZ(), see:

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz

I seem to recall we talked about tz's a while back ... and I can't remember whether you have installed MySQL's tz tables. If not, you won't be able to use symbolic names, like "US/Eastern", you'll have to use the numeric values, like "+05:00".

Unfortunately, MySQL doesn't install with those tables automatically, and almost no hosts install them for you, so unless you manually install them (which you may or may not be able to do, depending on your hosting arrangement), you can't use symbolic names.

The main advantage of using symbolic names is that CONVERT_TZ() will then take daylight savings into account, which using numbers won't. WHich often isn't a problem, but if you do need DST ... you'll need to install those tables.

-- hugh

 

[keianrad]

Thank you for your reply. Is it possible to convert TZ based on user local time. as we have several time zones and we need to show records based on user local time.

 

[cheesegrits]

Probably.

Are you talking about doing this in your own custom code, or in a Fabrik prefilter?

It would be possible to do in custom code, but if you don't have the MySQL TZ tables installed, it's not trivial. The problem is that J! uses PHP's time zone strings, like "America/Chicago", so unless you have the TZ tables installed in MySQL, you'd have to manually work out the offset hours in PHP, convert the raw seconds into the +/-x:xx format MySQL wants, and use that in the query.

// get user, global config objects
$user = JFactory::getUser();
$config = JFactory::getConfig();
// get the user's tz string, use global tz if not set
$tz = $user->getParam('timezone', $config->get('offset'));
// create a PHP tz object
$tz = new DateTimeZone($tz);
// get current datetime in this tz
$date = new DateTime("now", $tz);
// get the tz offset in seconds
$offset = $tz->getOffset($date) . ' seconds';
// get the difference interval between time now and offset time
$dateOffset = clone $date;
$dateOffset->sub(DateInterval::createFromDateString($offset));
$interval = $dateOffset->diff($date);
// format the interval in the format MySQL wants (+/-x:xx)
$mysqlTz = $interval->format('%R%H:%I');
// now you can use $mysqlTz in a where clause, like ...
$query->where('CONVERT_TZ(due_date, "+0:00", "' . $mysqlTz . '") BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()');

If you do have the MySQL tz tables installed, it's much easier ...

// get user, global config objects
$user = JFactory::getUser();
$config = JFactory::getConfig();
// get the user's tz string, use global tz if not set
$tz = $user->getParam('timezone', $config->get('offset'));
$query->where('CONVERT_TZ(due_date, "GMT", "' . $mysqlTz . '") BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()');

To make this a little easier, if you update from github, I've added some helper function, so you could just do this (for no tz tables using numbers):

$query->where->('CONVERT_TZ(due_date, "+0:00", "' . FabrikWorker::getUserTzOffsetMySql() . '") BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()');

... or this for symbolic names (if you have tz tables):

$query->where->('CONVERT_TZ(due_date, "GMT", "' . FabrikWorker::getUserTzName() . '") BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()');

-- hugh

 

[cheesegrits]

If, on the other hand, you need to do it in a Fabrik prefilter, I would have to figure out a way to do that, which would probably involve adding something to our placeholder handling, so something like {$my->tz->name} and {$my->tz->offset} would give you the symbolic and numeric TZs.

-- hugh

 

[keianrad]

Thank you Hugh, you are great. actually I decided to transfer my websites to the hosting server that you recommended as the current one doesn't work properly....!

I need to use this code in the custom code for reports that you wrote and fabrikar pre-filter in lists, menus and modules as they must return same results. It would be good if you add these feature to fabrikar.

 

[cheesegrits]

I'll look at adding some date handling placeholders once I've gotten the 3.8.1 release out.

-- hugh