Database storage in wrong TMZ [#210720]

I have a legacy system I've imported into Drupal with timestamps based off my local timezone. My CCK setup includes the Datestamp field + calendar. When creating/updating new content I enter the date (m/d/Y) I want, but in the database the timestamp is altered to be GMT. I need my dates to be all in the same TMZ, and I would prefer it's based on mine and not GMT. Any way I could get this to work?

An example:
- I enter 1/16/2008 into the field and submit my form
- In the database it's stored as 2008/1/15T17:00:00 (except as a timestamp)
- When I query my data directly and use date('m/d/Y', $result->field_timestamp_value);, my date is returned as a day earlier than what I had input.

Comments

Comment #1

ChrisRL CreditAttribution: ChrisRL commented 2 February 2008 at 23:55

Local times are often not unique. If your zone has Daylight Savings Time, or Summer Time, or anything similar, there are times that overlap with each other. It isn't possible to store these times as a local time without being ambiguous. Calculations of any kind (apart from calculations intended directly for display) should not be done on local times for this reason.

When you query your data, use the Date API functions for loading a GMT/UTC date.

example for Date API 1.x:

$formatted_date = date_format_date('m/d/Y', $result->field_timestamp_value, NULL, $local_timezone);

example for Date API 2.x:

$date_obj = date_make_date($result->field_timestamp_value, 'UTC', DATE_UNIX);
date_timezone_set($date_obj, timezone_open($local_timezone));
$formatted_date = date_format_date($date_obj, 'custom', 'm/d/Y');

You may use date_default_timezone_name() for $local_timezone.

Comment #2

KarenS CreditAttribution: KarenS commented 5 September 2008 at 13:26

Status:

Active

» Fixed

I'm not making changes to the 5.1 version. I'm officially recommending you move to the 5.2 version now. If you have problems in that version you can check for existing issues or add new ones. Feature requests are now getting posted to the D6 version to be back-ported to 5.2.

Timezone handling is much improved in 5.2.