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
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:
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)
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!