Default date_time storing wrong time

[rackem]

It looks like the default date_time element is not storing UTC time.

I have a list where I use Fabrik to enter some records and also manually create some using the Form PHP Plugin. In the plugin, I use the mySQL function UTC_TIMESTAMP().

Right now, my local time is about 6 AM CDT (GMT-5).
If I enter a record through Fabrik I get: 2013-07-30 16:00:00
If I use UTC_TIMESTAMP(): 2013-07-30 11:00:00

So it looks like instead of UTC, Fabrik is storing local time with an opposite timezone offset.

 

[troester]

Confirmed.
I have server and Joomla time = Zurich

the default date_time element (only set to hidden=no, rest are default settings: default to current, store UTC)
is showing the correct local time in a new record (16:50:04), but in DB it is 12:50:24
so it's not storing the displayed time in UTC (would be 14:50:04) but the "storing" time (20 secs later) and a wrong timezone offset (-4 hours, 2x UTC offset?)

Then the list is showing the correct local time (14:50:24, correct related to the time wich is now in the DB), details view and edit form, too.
but after save the DB has 12:50:00 (so correct UTC offset, but cutting the seconds)
same if you are editing the time: UTC offset is ok, seconds cut (they are cut already if you are leaving the date field)

So there are two issues:
minor: seconds are cut
big one: wrong time offset in new records with "default to current"

Same if date element is set to "store local time":
default time of new record is stored with wrong time offset (-2 hours, UTC?)

If "default to current"=no, all is as expected (store as local or UTC) (beside the cutted seconds).

 

[cheesegrits]

Raised an issue on thisone:

https://github.com/Fabrik/fabrik/issues/845

-- hugh

 

[rackem]

*friendly bump* for a fix on this

 

[Sophist]

I also raised an issue that date field is storing current time against the date you select in the picker rather than time of zeros.

 

[rob]

So it looks like instead of UTC, Fabrik is storing local time with an opposite timezone offset.

What I see is that the timezone offset was applied twice, so once to set to the UTC, and then (incorrectly) once again.
This has been fixed I hope in the latest github commit

 

[rackem]

Hmm, not there yet but different behavior now.

I created 3 records at through Fabrik at different times however the same date time is on each. It looks like it is applying the timezone offset from a time of 00:00:00.

Right now, my local date time is about 2013-08-08 7:45 PM CDT (GMT-5).
Through Fabrik: 2013-08-08 05:00:00
UTC_TIMESTAMP(): 2013-08-09 00:46:40

 

[rackem]

*friendly bump*

 

[Sophist]

I can see what is happening I think. I asked Rob to set the time to zeros if you don;t show the time selector, and it is perhaps now doing this and then applying the offset to UTC (hence the 05:00:00).

Handling this correctly in all circumstances is going to be difficult I fear. For example, if you stored it with zero time, then when you subtract the time difference on reading the database, the date would be 1 day less. I guess you could check on read whether you were going to display the time, but then you would get different results if you turn on time display. Perhaps the best solution would be to allow the user to select between date and datetime formats in the database, and only apply time-zone offsets in both directions if the user has selected datetime.

S

 

[rackem]

Note this is the default date element created whenever you make a new list. So it is set to be hidden and defaults to current date. The user never interacts with it, I just need to have an accurate date-time when the record was created. Having a timezone offset for display would be nice but isn't completely necessary.

 

[Sophist]

Ah - this seems to be an unintended consequence of one of Rob's changes.

You can try changing this to timestamp or try enabling the time part of the datetime (even though the whole thing is hidden.

S

 

[rackem]

Enabling the time selector (even though the element is hidden) works! The proper UTC time is saved. Thank you Sophist!

That serves as a workaround. Or perhaps the solution?

 

[Sophist]

No - I think the solution is to allow the datetime field to have an option for setting date or datetime fields in MySQL. Or for what you want, to use the timestamp field which is designed for this.