Hi folks,
apologies for this stupid question, but I cant get the timezone to work in SQL query..... I have tried for ages... can you suggest anything?
The date is in UTC and I want to format it to Australia/Sydney (+10:00 hours).
Among other things, I have tried the embedded "SELECT CONVERT_TZ(mydate,'US/Pacific','CET') FROM mytable" construct (replacing the timezones).... just cant crack this!
Using Joomla 3.x and SmartContent 1.9.4, and using the standard Jomla users table.

Any suggestions will be gratefully appreciated... and apologies again for the stupid question!

Regards,
Mike

My actual base code in the module (which works but displays UTC), is as follows, where the date I want converted is called lastvisitDate:

{arisqltable}
{ariconnectionstring dbHost="localhost" dbUser="myhost_myid" dbPass="mypass" dbName="myhost_mydb" dbType="mysqli" persistent="false"}
{arisqltablecolumns}
{arisqltablecolumn id="ID" alias="ID" width="20%" className="idg"}
{arisqltablecolumn id="USERNAME" alias="USERNAME" headerClassName="head"}
{arisqltablecolumn id="NAME" alias="UserName" headerClassName="head"}
{arisqltablecolumn id="EMAIL" alias="EMAIL" width="20%" className="idg"}
{arisqltablecolumn id="LASTVISITDATE" alias="LASTVISITDATE" width="20%" className="idg"}
{arisqltablecolumn id="REGISTERED" alias="REGISTERED" headerClassName="head"}
{arisqltablecolumn alias="Virtual Column" headerClassName="head" virtual="true"}
{coltemplate}{$NAME} - {$ID}{/coltemplate}
{/arisqltablecolumn}
{/arisqltablecolumns}
{arisqltablequery}
SELECT username AS 'LOGON NAME', id AS ID,name AS NAME, email AS EMAIL, registerDate AS REGISTERED, lastvisitDate AS 'LAST VISIT DATE'
FROM myprefix_users
ORDER BY lastvisitDate DESC
{/arisqltablequery}
{/arisqltable}

Hello,

Try the following query:



Regards,
ARI Soft

That worked absolutely perfectly...........

Thank you so, so much for that.
You have helped save my sanity....

regards
Mike