Timestamp element not saving as GMT

[rackem]

The Timestamp element is not respecting the GMT or Local Server time setting. It always saves the local server time.

 

[rackem]

*friendly bump* Can anyone confirm?

 

[rackem]

*friendly bump*

 

[dimoss]

I confirm the problem that rackem describes.
Thanks!

 

[cheesegrits]

I seem to recall going through trying to fix this before. We tried to add that option on request from a user, but I have a feeling it turned out to be impossible, and the MySQL TIMESTAMP will only ever write local time.

So really the only solution to this is to remove that option.

Or make it "Display as UTC / local", instead of "Store as".

-- hugh

 

[rackem]

MySQL TIMESTAMP will only ever write local time.

I disagree as I use MySQL to do this right now. Here is a little background on my project.

I have a "ratings_history" list that contains pool player ratings. Each record needs a timestamp so I can retrieve the most recent record. The initial "player rating" is entered using the Fabrik form for the "ratings_history" list. Subsequent "player ratings" are based on information from a different form so I use the Form PHP with a MySQL INSERT statement to create those records in the "ratings_history" list. That code uses the MySQL function UTC_TIMESTAMP().

I discovered the reported issue because the records that Fabrik created were several hours offset from the ones from my Form PHP code. I realized the timestamps from Fabrik (using the timestamp element) match my current local time. The timestamps on records created using UTC_TIMESTAMP() match current UTC/GMT. I also tried using the date element but that wasn't working either so I was recommended to use the timestamp element instead as that was really its intended purpose.

So all that really matters here is that the timestamp output (local or GMT) I use in Fabrik must match the one that I use in my Form PHP. I am fine adapting my code to work with the Fabrik output but since the Fabrik output is different from what it says it should be doing, it would "break" when the Fabrik code is fixed.

I looked around in the Fabrik code but couldn't determine where that information is set. I can figure out basic code but the stuff going on behind the scenes in Fabrik is still way over my head. The timestamp.php code has a render function on line 78 but that seems to be what is displayed, not what is recorded to the db.

 

[cheesegrits]

The reason you can't find where we set a time stamp in the timestamp.php is we don't. We use the TIMESTAMP data type, which automagically sets the timestamp when a row is saved, not the TIMESTAMP() or UTC_TIMESTAMP() functions:

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

... which, as far as I can tell, always writes local time. As you disagree with that, if you can tell me how to get the TIMESTAMP data type to use UTC, I'll happily modify our code to do it.

I'm working on the date element this weekend to resolve some wrinkles, that may be the way you need to go once it's saving consistently.

-- hugh

 

[cheesegrits]

BTW, the timestamp element doesn't get much love and care from us, because it proved to be not as useful as we'd hoped. Mosty because you can only have one TIMESTAMP field in any one table. Also because of the local / UTC issue.

We may at some point create another timestamp element, which does it by steam rather than using the TIMESTAMP field type, and offers a couple of choices, like using a DATETIME field or a simple integer and saving UNIX style epoch time.

-- hugh

 

[rackem]

As you disagree with that, if you can tell me how to get the TIMESTAMP data type to use UTC, I'll happily modify our code to do it.

That was a poor choice of wording on my part, sorry. I should have said, "I don't understand, would you please explain. " Which you kindly did, thank you. I understand why it operates like it does now.

I'm working on the date element this weekend to resolve some wrinkles, that may be the way you need to go once it's saving consistently.

I will keep an eye on your progress with the date element, thanks for the head's-up.

In the meantime, I have changed my Form PHP code to use NOW() instead of UTC_TIMESTAMP() which allows it to function as intended.