How to use convert_tz in a joomla query?

Question

This is my current query:

$uvdatetime = JFactory::getDate('now', new DateTimeZone('Europe/Athens')); 

$db = JFactory::getDbo();
$query = $db->getQuery(true); 
$query->select("distinct ". $db->quoteName('catid'));
$query->from($db->quoteName('jos_k2_items'));
$query->where($db->quoteName('publish_up') .' <= '. $db->quote( $uvdatetime ));

I need to convert the timezone of the publish_up field from UTC to EET in order to compare correctly with $uvdatetime.

After searching, I tried to use compare_tz like below.

$query->where convert_tz($db->quoteName('publish_up'),'GMT','EET') .' <= '. $db->quote( $uvdatetime );

However, I get the following message:

unexpected T_STRING

Is there a way to use convert_tz in a joomla query?

If not, is there a different/better way to accomplish this conversion?

EDIT: i have to clarify the purpose of this code to avoid confusion
my initial goal is to give a class style to a menu item when an article is written TODAY.
The problem is that if an article is saved with a publish date between 00:00 and 03:00 (EEST) joomla saves it as UTC.
so for example: "2018-09-04 01:00:00" is saved as "2018-09-03 22:00:00". it goes back one day...
this is what i am trying to convert. the time and the date. i was using a much simpler query:

$query = "SELECT DISTINCT catid FROM jos_k2_items WHERE DATE(publish_up)>=CURDATE()";

but still.. convert_tz doesn't work

Final steps in order @mickmackusa's second solution(and definitely the best) to work

Specs: Centos 7.5, MariaDB 5.5, plesk 17.8

we need to load the timezones to mysql.(i followed this excellent post)

i run SELECT CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York'); and it returned null. this means time zones are not loaded.

so i logged with ssh and give

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u admin -p`cat /etc/psa/.psa.shadow` mysql

remember this is a plesk installation. use your "-u root" and your mysql password if not plesk. you may get 2 warnings

Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

based on this you are good to go.

now zones are loaded. you can run the previous query to test it.

and now we can use mick's query to convert the timezone properly.

 $query->where("DATE(CONVERT_TZ(publish_up, 'GMT', 'Europe/Athens')) = DATE(CONVERT_TZ(NOW(), 'GMT', 'Europe/Athens'))");

had to change EET with Europe/Athens

pretty awesome. cheers!

Answer 1

Rather than using CONVERT_TZ(), you can use INTERVAL in your query to execute a daylight-savings-ignorant conversion and avoid the use of a php variable.

$query->where("DATE(publish_up + INTERVAL 3 HOUR) = DATE(NOW() + INTERVAL 3 HOUR)");

This will bump the current time and the publish_up time forward so that you can determine which rows fall on the current day.

Here is an sqlfiddle: http://sqlfiddle.com/#!9/859cd7/4

---

However, if you need the accuracy of a daylight-saving-sensitive solution, then you can use CONVERT_TZ() on both sides of the comparison, but you may need to update your database's timezone table to handle the abbreviations (you can search Stackoverflow or the greater web to find instructions on this).

$query->where("DATE(CONVERT_TZ(publish_up, 'GMT', 'EET')) = DATE(CONVERT_TZ(NOW(), 'GMT', 'EET'))");

*In your original snippet, you need to pass the [convert_tz()][1] function as a string into the query builder. You were missing an some parentheses and quoting.

*publish_up doesn't technically require backticks, so your query will be easier to read if omitted.

Answer 2

There are obvious php syntax errors in what you have tried. The convert_tz is a MySQL function and you need it to pass it inside the query as a tring to be processed by MySQL.

Try something like below:

$query->where('convert_tz('. $db->quoteName('publish_up').', "+00:00","+03:00") <= '. $db->quote( $uvdatetime ));

---

Finally you may want to consult MySQL docs about using named time zones with the convert_tz function