[SOLVED] Dates saving to the day before and time is always 12:00

[chozma]

Hi guys,

I have a problem with my date elements. I suspect it is related to our server settings but I would be grateful if you guys had any guidance you could share, any bug testing etc I could try. I've never been very good/interested in servers so someone else manages this in NZ. By the way, our server is currently set to NZ time (Auckland).

Basically, I have lots of different date fields spread around a number of different forms across my site. On save it seems they are saving to a day earlier than inputted. I haven't tested every single date yet (there are a lot throughout my site) but I've tried five of them and their behaviour is all the same. Saving to a day earlier.

I also have a lot of my date elements set to not show the time to the user. I am sure that before it used to save the current time, but now the time is always saving to 12:00. I don't really need the time for anything but I don't know if it helps provide a clue as to what is going on.

A few of my date elements are set to auto display today's date (which you can then edit) and it is displaying the right date (eg today's date) to start with.

Additionally, I also have a couple of bespoke bits of code I have written that when you save a form it auto-updates some of the inputted dates in totally separate list. These dates are saving correctly as the date you entered and not a day earlier. They also save the current time rather than 12:00.

So I come to the conclusion that something is being changed by the system (Fabrik core or otherwise) and I'm not really sure where to go from here.

Any bright ideas??

Thanks as always,

Hannah

 

[troester]

This is the expected behaviour.
If you set no time format in your date element it's the (local) date + time 00:00 e.g. 2015-09-03 00:00

If your date element is set to store as UTC (the default) this is converted to UTC for storing in the DB.
NZ is UTC + 12?, so your date in the DB is 2015-09-02 12:00 (= 2015-09-03 00:00 - 12 hours)

But it should show the correct (local) date in Fabrik forms and lists.

 

[cheesegrits]

So I guess the only question is, are they displaying correctly in lists and forms?

As Troester says, if your date is set to "Save as UTC", then in the underlying database, they will be in UTC - we remove the TZ offset from the date prior to writing it to the database, and we (should) re-apply the offset when displaying it. This is the default for dates in Fabrik, and is done to allow dates to be "portable", so you don't need any a-priori knowledge of what TZ they were created in. You can export the data, import it on a server in a different TZ, and the dates will display correctly, relative to the TZ in which they were input.

-- hugh

 

[chozma]

Brilliant, thanks Troester and Hugh. I do so appreciate your patience in explaining things to me.

You've helped me have an epiphany with this.

I've realised that the dates are indeed correctly displaying in the Fabrik lists and tables but there are several places where I pull out dates directly from the database using JDatabase queries or store them. It is those queries that are displaying incorrectly as they are not applying the TZ stuff.

So one question is.... if I am pulling dates directly out of the db or putting them in using JDatabase is there a quick way of applying the TZ stuff that Fabrik uses?

 

[cheesegrits]

The best way to do it kinda depends what you need to do with it, but the easiest way to apply the TZ is to use J!'s date API.

So if $gmt_date is what you've pulled out of the database in standard MySQL date format ...

        $timeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));
        $date = JFactory::getDate($gmt_date, $timeZone);

Now you've got $date as a J! date object, and can use any of those API methods to do what you need with it. So to get it back as MySQL format ...

$local_date = $date->toSql(false);

.... or to format it using standard format strings (assuming your format string is in $format) ...

$formatted_date - $date->format($format, true);

... where the second arg true will translate any text generated by the format, like month names.

if you need to take a date in local time and convert it to GMT prior to storing in the database:

        $timeZone = new DateTimeZone(JFactory::getConfig()->get('offset'));
        $gmt_date = JFactory::getDate($local_date, $timeZone)->toSql(true);

Here's the JDate API doc:

https://api.joomla.org/cms-3/classes/JDate.html

NOTE that it extends the standard PHp DateTime class, so you can use anything from standard PHP DateTime as well.

-- hugh

 

[chozma]

Thanks Hugh - this was a great starting point for me and I have managed to get my head around it all. I now have things working as I want! Well hopefully, let's see what the testers say!