Jeff VIP
Total posts: 745
17 Mar 2016 12:12

Hi,

My site is hosted by Siteground where the Server Time is SGT(+8) Asia/Singapore. The Server Time Zone in Joomla's Global Configuration is set to Adelaide (+10:30).

I have noticed that subscriptions start/end dates are saved in SGT time in the database, but on the site 10:30 hours are added instead of 2:30 hours (10:30-8=2:30 ) resulting in some subscriptions starting the next day.

I have edited a normal Joomla article and the modified time was saved in GMT(+0) time, which I think is correct timezone to store dates.

Last Modified: 09 May 2022


hakana VIP
Total posts: 425
18 Mar 2016 20:57

I think what you state is a server based Joomla issue, not a component issue. What happens when you post an article? Are the dates correct then?


Jeff VIP
Total posts: 745
21 Mar 2016 00:12

hakana I think what you state is a server based Joomla issue, not a component issue.

Joomla has no issues what so ever with my settings.

hakana What happens when you post an article? Are the dates correct then?

Yes. Articles are saved in UTC/GMT time in the database.

I spend 2 days checking and double checking this issue with phpMyAdmin, The World Clock – Time Zone Converter and displaying dates in different formats . My findings: Emerald is saving dates not in UTC time but in SGT time.


Sergey
Total posts: 13,750
23 Mar 2016 02:45

This is how I add create date to subscription

$db->setQuery("SELECT FROM_UNIXTIME($start)");
$this->ctime = $db->loadResult();

and this is how it is done in com_content

$date = JFactory::getDate();
$this->created = $date->toSql();

Emerald takes time from Mysql and Joomla from PHP.

Why? because we had a lot of problems with time in the past. Different settings. Many people do not understand Time Zone setting in Joomla and use it wrong. So there become a difference between PHP and MySQL time and when in the query we check access to sunscription we use WHERE extime >= NOW() we get wrong result.

What we did is, create time from MySQL and when we compare and check in MySQL query we always get correct result.

I can only suggest you to set Joomla TimeZone setting right. It is not exactly TimeZone. It is more Time offset. I mean Joomla think that your server time is UTC and TimeSone setting just add offset to that time. If your server time alreade +5GMT for example and you set Joomla TimeZone +5 then you get +10 at the end.


Jeff VIP
Total posts: 745
23 Mar 2016 06:44

Thanks Sergey for explaining.

Changing the Joomla TimeZone would affect the time display of other components too, wouldn't it? So I don't think this is a solution to my problem.

Strangely, Emerald doesn't respect the Joomla TimeZone when it comes to displaying dates. For example, in the Subscription Confirmation Email, the Start and End date are displayed in the MySQL TimeZone, which is very confusing for the end user. The same is true for the Subscription History page and the Sales page.

I think I can adjust the date output via template overrides:

$cdate = new DateTime($item->ctime, new DateTimeZone('SGT'));                                
$cdate->setTimezone(new DateTimeZone('Australia/Adelaide'));
echo $cdate->format('d/m/Y H:i:s T');

I'm not sure how to do this for the emails, though.


Sergey
Total posts: 13,750
23 Mar 2016 08:42

May be I have to add separate timezone parameter to Emerald. Or you just make sure your PHP and MySQL in the sime timesone. Or mysql NOW() and php date('Y-n-в H:i:s') are equal.


Jeff VIP
Total posts: 745
23 Mar 2016 09:57

Sergey May be I have to add separate timezone parameter to Emerald.

That would be ideal, since Emerald has a non Joomls standard way of storing dates. People without the TimeZone issue could leave the parameter empty and I could set a Timezone offset.

Sergey Or you just make sure your PHP and MySQL in the sime timesone.

Already done that through php.ini but this doesn't affect Emerald's time display

Sergey Or mysql NOW() and php date('Y-n-в H:i:s') are equal..

Huh?


Sergey
Total posts: 13,750
23 Mar 2016 14:15

I am waiting for you in skype.


pepperstreet VIP
Total posts: 3,834
23 Mar 2016 16:24

Very interesting discussion and important issue.
Screams for a docs article with usecase examples and tips.

Sorry, I always need a "picture" to get my head around things ;)


hakana VIP
Total posts: 425
23 Mar 2016 22:36

We have discussed this issue with Sergey before and he fixed the problem. And I checked it and never saw an issue since then. I don't understand how this issue came up again.

Please find the old topic here:
www.mintjoomla.com/support/community-forum/user-item/3923-hakana/52-emerald-9/4616-wrong-sales-time-in-admin-area.html


Jeff VIP
Total posts: 745
24 Mar 2016 00:55

hakana I don't understand how this issue came up again.

Do you have this issue again on your website?


Jeff VIP
Total posts: 745
27 Mar 2016 01:03

Sergey has added the option to insert [START+0] in the email template to output the date acoording to the Joomla TimeZone settings. All other dates can be manipulated through the templates. In short: Emerald still saves AND outputs dates in the MySQL timezone but the output can now be adjusted in the templates as well in the emails.


hyvyr
Total posts: 1
09 May 2022 18:21

Lab tests have shown that under intense pressure our cerebrums look for a fitting https://survivalhacks.co/beginners-guide-to-fishing-hunting-and-trapping-for-survival/ reaction and in some cases pick some unacceptable one. It is actually similar to deer that freeze in the headlights of a vehicle.

Powered by Cobalt