Date or DateTime in database for Component Development using timezones?

Question

Our component manages Television programming, so dates & times are critical - here's the scenario:

1. Our server and most producers are all on local, Pacific Time (America/Los_Angeles, UTC+8)
2. Joomla is configured with the timezone being "America/Los_Angeles" (public $offset = 'America/Los_Angeles';) because that IS the timezone we're in.
3. The database has a date field specifying when the episode is scheduled to be aired - the actual time is unknown.
4. It also has a datetime field specifying when the episode was actually aired, including the time it was aired.

The episode in question has a scheduled date of "2015-01-02" (time is unknown). Using a Calendar field type, it always displays 2015-01-01, presumably because it's defaulting to 00:00:00 as the time and subtracting 8 hours due to the offset.

So, the question comes down this is:

1. Does the calendar field type have a bug because it's a Calendar, not a clock, so should not be considering the time,
2. or should we have Joomla configured to use the UTC time zone?
3. or am I missing something fundamental here?

And finally, if the displayed date is going to "fluctuate", is there a best-practices method to advise the user which time zone is in play? i.e. The episode is going to air Jan 2nd local time, yet the calendar is displaying Jan 1st.

I apologize for the general confusion of the question - I've been chasing this for a bit, and am not sure of what the Timezone premises are that Joomla runs on, so what the expected output should be.

Additionally:

I'm not using JHtml::calendar directly, but via my form:

<field name="first_aired" type="calendar"
    label="COM_PASS_EPISODES_EPISODES_FIRST_AIRED_PLANNED_LABEL"
    description="COM_PASS_EPISODES_EPISODES_FIRST_AIRED_DESC"
    class="required span2 validate-airdate"
    default="NOW"
    format="%Y-%m-%d"
    />

The code in libraries\joomla\form\fields\calendar.php does it's thing, then simply uses

return JHtml::_('calendar', $this->value, $this->name, $this->id, $format, $attributes);

Further Discovery

The Calendar Field docs say that only SERVER_UTC or USER_UTC are allowed for the optional filter element, and omitting it or specifying "" defaults to USER_UTC, but specifying anything invalid skips the code that adds the offset, so show the actual date regardless of having a time or not.

Answer 1

As Lodder stated above in the comments - the calendar field being saved to DB in UTC. But... only if you use filter="USER_UTC" or filter="SERVER_UTC" in the XML and then validate your form using validate() method. The magic is happening in the filterField() method of JForm class which converts the value to UTC.

When you set up the date you should always think about your local time. So if your episode has a scheduled date of "2015-01-02" (UTC), your local time is UTC and server is running "America/Los_Angeles" (UTC-8) and you set "2015-01-02" then the calendar field should display "2015-01-02" for you, but not "2015-01-01". If you will try to change your timezone to UTC-1 for example, it should display "2015-01-01", because it will be "2015-01-01 23:00:00". But for all on the website it will be "2015-01-01 16:00:00" (UTC-8).

But there is a strange behavior. When you are omitting this filter the display value defaults to USER_UTC. It is ok. But when you validate the form it will not use USER_UTC as filter and save the value as is. The result is obvious and confusing - the calendar field will always shift the time. So if you are omitting it you should convert to UTC by yourself.

Is it a bug? I am not sure.

P.S. I feel that we should add this to doc?