Joomla is storing wrong date

Question

I know it's a server/joomla feature and not a bug but I am storing a date via the user profiles and when I look in phpmyadmin the date stored is always one day behind BUT when I view the value in Joomla it is the correct date.

I need the correct value stored in the database far more than I do in the Joomla display.

How can I get Joomla to store the correct date as selected.

The following code works and outputs the date for the different offsets:

echo 'Australia/Sydney' . JFactory::getDate('now', 'Australia/Sydney');
echo "<BR>";
echo 'UTC' . JFactory::getDate('now', 'UTC');
echo "<BR>";
echo 'Asia/Jakarta ' . JFactory::getDate('now', 'Asia/Jakarta');

But this code

echo 'Australia/Sydney' . JFactory::getDate('today', 'Australia/Sydney');
echo "<BR>";
echo 'UTC' . JFactory::getDate('today', 'UTC');
echo "<BR>";
echo 'Asia/Jakarta ' . JFactory::getDate('today', 'Asia/Jakarta');

All gives me the same result : 2015-04-29 00:00:00

How can I get joomla to give me a date with the correct timezone offset please?

Answer 1

Joomla shows dates according to general Server Settings/Server Time Zone configuration or specific User Time Zone.

In your database, all dates are in UTC timezone. Then, when a date shown, it is converted to the appropiate timezone.

If you change the general time zone or a user time zone, dates in your database will remain the same and shown dates will be updates.

If you are saving dates directly to the database, it is recommended to follow the same definition. These conversion routines are very handy:

Converting date to user timezone in Joomla

Answer 2

OK so I believe this to be true :)

I was looking for a Joomla answer whereas I needed to use some Joomla and some php to get what I wanted.

JDate works off UTC timezone by default.

So you need to apply your server timezone offset (or user timezone offset) to the date first. That way,

$config = JFactory::getConfig();
$offset = $config->get('offset');
echo $offset;
echo "<br>";
$date = new JDate('today', $offset);
echo $date;
echo "<br>";
$date->setTimezone(new DateTimeZone('UTC'));
echo $date;
echo "<br>";

The output of the above for me was: Australia/Melbourne 2015-04-30 00:00:00 2015-04-29 14:00:00

and now I have the UTC offset of today for my server and can use it query data.