I am hoping to make this question and the answers to it the definitive guide to dealing with daylight saving time, in particular for dealing with the actual change overs.

If you have anything to add, please do

Many systems are dependent on keeping accurate time, the problem is with changes to time due to daylight savings - moving the clock forward or backwards.

For instance, one has business rules in an order taking system that depend on the time of the order - if the clock changes, the rules might not be as clear. How should the time of the order be persisted? There is of course an endless number of scenarios - this one is simply an illustrative one.

How have you dealt with the daylight saving issue?

What assumptions are part of your solution? (looking for context here)

As important, if not more so:

What did you try that did not work?

Why did it not work?

I would be interested in programming, OS, data persistence and other pertinent aspects of the issue.

General answers are great, but I would also like to see details especially if they are only available on one platform.

This is one of the best practical questions I've seen on SO. This is also a very painful issue. IMHO, If everyone plays along, sharing their experience with DST issues, this page could eventually turn into an invaluable resource. +1.
–
M.A. HaninMar 28 '10 at 12:58

@abatishchev - This way GETDATE() on SQL will be UTC (as will DateTime.Now). And the server will not be effected by any sort of automatic DST changes.
–
Oded♦May 31 '12 at 15:23

2

@Oded: I can agree if "on server" will be added. But still that can affect another applications which need local time. By this and other reasons I think it's better to request Utc time explicitly.
–
abatishchevJun 1 '12 at 6:47

2

UTC is preferred to GMT, both because it is more precisely defined and because GMT definitions are messed up on some operating systems. It's common for people to treat the terms "GMT" and "UTC" as interchangeable but they aren't entirely. For almost any software / systems purpose, use UTC. See stackoverflow.com/questions/2292334/…
–
Chris JohnsonOct 1 '12 at 10:04

26 Answers
26

Summary of answers and other data: (please add yours)

Do:

Whenever you are referring to a particular moment in time, persist the time according to a unified standard that is not affected by daylight savings. GMT and UTC have been mentioned by different people, though UTC seems to be mentioned most often.

?? Include the local time offset as is (including DST offset) when storing timestamps. ?? Store timestamps as UTC or epochs(number of seconds since 1970) without time zone or offset.

Include the original time zone name, so you can reconstruct the original time at a later point and display correct offsets if needed.

Remember that DST offsets are not always an integer number of hours (for example, Indian Standard Time is UTC+05:30).

If using PHP > 5.2, use the native time zones conversions provided by DateTime, and DateTimeZone classes. Be careful when using DateTimeZone::listAbbreviations(); see answer. To keep PHP with up to date Olson data install periodically this PECL package; see answer.

Create a table TZOffsets with three columns: RegionClassId, StartDateTime, and OffsetMinutes (int, in minutes). See answer

Business rules should always work on civil time (UTC/GMT).

Internally, keep timestamps in something like civil-time-seconds-from-epoch. See answer.

Only convert to local times at the last possible moment.

Remember that time zones and offsets are not fixed and may change. For instance, historically US and UK used the same dates to 'spring forward' and 'fall back'. However, in the mid 2000s the US changed the dates that the clocks get changed on. This now means that for 48 weeks of the year the difference between London time and New York time is 5 hours and for 4 weeks (3 in the spring, 1 in the autumn) it is 4 hours. Be aware of items like this in any calculations that involve multiple zones.

Consider the type of time (actual event time, broadcast time, relative time, historical time, recurring time) what elements (timestamp, time zone offset and time zone name) you need to store for correct retrieval - see "Types of Time" in answer.

Check if your DBMS needs to be shutdown during transition.

Keep your OS, database and application tzdata files in sync, between themselves and the rest of the world.

My Answer:

Always persist the time according to a unified standard, preferably UTC.

UTC is agnostic to daylight saving time and as such it is a good baseline. Once you have that, displaying the proper time according to time zone, DST, etc... is just a matter of applying the proper delta.

Using GMT doesn't really solve the problem, you still need to figure out what is the right delta to apply, and this is where all the complexity lies. The delta can be complex to determine in systems that are used by users in different regions (with different daylight saving switch schedules) or in systems showing historical/future data.
–
ckarrasMar 28 '10 at 14:40

7

That's true if all the application needs to do is to display the current local time. But if we have for example a server in Australia that needs to display the date/time for a transaction in Canada on April 2 2006 (This was the last year before the daylight saving switching rules changed in Canada) - do you have an OS call that can do DateTime("2006/04/02 14:35Z").ToLocalTime().WithDaylightSavingRules("Canada",2006) ?
–
ckarrasMar 28 '10 at 15:00

Keep in mind converting future dates (even just tomorrow) to UTC you always lose something. For example, you used some known offset to make that conversion, but since the date is in the future, there is always a chance the group that sets those rules will change those rules. Also, it is virtually impossible to convert some future dates to UTC because the daylight savings times are not known until that year (some countries set the dates every year, not 10+ years ahead or based on any set rules).
–
eselkFeb 21 '13 at 15:39

I'm not sure what I can add to the answers above, but here are a few points from me:

Types of times

There are four different times you should consider:

Event time: eg, the time when an international sporting event happens, or a coronation/death/etc. This is dependent on the timezone of the event and not of the viewer.

Television time: eg, a particular TV show is broadcast at 9pm local time all around the world. Important when thinking about publishing the results (of say American Idol) on your website

Relative time: eg: This question has an open bounty closing in 21 hours. This is easy to display

Recurring time: eg: A TV show is on every Monday at 9pm, even when DST changes.

There is also Historic/alternate time. These are annoying because they may not map back to standard time. Eg: Julian dates, dates according to a Lunar calendar on Saturn, The Klingon calendar.

Storing start/end timestamps in UTC works well. For 1, you need an event timezone name + offset stored along with the event. For 2, you need a local time identifier stored with each region and a local timezone name + offset stored for every viewer (it's possible to derive this from the IP if you're in a crunch). For 3, store in UTC seconds and no need for timezones. 4 is a special case of 1 or 2 depending on whether it's a global or a local event, but you also need to store a created at timestamp so you can tell if a timezone definition changed before or after this event was created. This is necessary if you need to show historic data.

Storing times

Always store time in UTC

Convert to local time on display (local being defined by the user looking at the data)

When storing a timezone, you need the name, timestamp and the offset. This is required because governments sometimes change the meanings of their timezones (eg: the US govt changed DST dates), and your application needs to handle things gracefully... eg: The exact timestamp when episodes of LOST showed both before and after DST rules changed.

Offsets and names

An example of the above would be:

The soccer world cup finals game
happened in South Africa (UTC+2--SAST)
on July 11, 2010 at 19:00 UTC.

With this information, we can historically determine the exact time when the 2010 WCS finals took place even if the South African timezone definition changes, and be able to display that to viewers in their local timezone at the time when they query the database.

System Time

You also need to keep your OS, database and application tzdata files in sync, both with each other, and with the rest of the world, and test extensively when you upgrade. It's not unheard of that a third party app that you depend on did not handle a TZ change correctly.

Make sure hardware clocks are set to UTC, and if you're running servers around the world, make sure their OSes are configured to use UTC as well. This becomes apparent when you need to copy hourly rotated apache log files from servers in multiple timezones. Sorting them by filename only works if all files are named with the same timezone. It also means that you don't have to do date math in your head when you ssh from one box to another and need to compare timestamps.

Also, run ntpd on all boxes.

Clients

Never trust the timestamp you get from a client machine as valid. For example, the Date: HTTP headers, or a javascript Date.getTime() call. These are fine when used as opaque identifiers, or when doing date math during a single session on the same client, but don't try to cross-reference these values with something you have on the server. Your clients don't run NTP, and may not necessarily have a working battery for their BIOS clock.

Trivia

Finally, governments will sometimes do very weird things:

Standard time in the Netherlands was
exactly 19 minutes and 32.13 seconds
ahead of UTC by law from 1909-05-01
through 1937-06-30. This time zone
cannot be represented exactly using
the HH:MM format.

This answer has some great points, I especially wanted to point this out part "Recurring time: eg: A TV show is on every Monday at 9pm, even when DST changes" Storing times in UTC in the DB and then converting for display helps handle a lot of the tricky aspects of dealing with time zones. However, handling "recurring events" that cross DST barriers becomes much tougher.
–
Jared HalesAug 5 '10 at 13:53

15

+1 for the Trivia. Keeping the content interesting makes this task more enjoyable, which may lead to increased productivity :)
–
ChrisJan 8 '11 at 6:54

A lot of good stuff in this answer, but a few issues. 1) Many time zone abbreviations are ambiguous. see here 2) Not always do you want to store in UTC. Most of the time - yes, but context matters. In your terms, television time would not be stored in UTC. Also, sometimes its unlawful or against policy to not store the local time - which is where things like DateTimeOffset (or equivalent) come in handy. Otherwise, good write up.
–
Matt JohnsonJan 11 '13 at 1:38

This is an important and surprisingly tough issue. The truth is that there is no completely satisfying stardard for persisting time. For example, the SQL standard and the ISO format (ISO 8601) are clearly not enough.

From the conceptual point of view, one usually deals with two types of time-date data, and it's convenient to distinguish them (the above standards do not) : "physical time" and "civil time".

A "physical" instant of time is a point in the continuous universal timeline that physics deal with (ignoring relativity, of course). This concept can be adequately coded-persisted in UTC, for example (if you can ignore leap seconds).

A "civil" time is a datetime specification that follows civil norms: a point of time here is fully specified by a set of datetime fields (Y,M,D,H,MM,S,FS) plus a TZ (timezone specification) (also a "calendar", actually; but lets assume we restrict the discussion to Gregorian calendar). A timezone and a calendar jointly allow (in principle) to map from one representation to another. But civil and physical time instants are fundamentally different types of magnitudes, and they should be kept conceptually separated and treated differently (an analogy: arrays of bytes and character strings).

The issue is confusing because we speak of these types events interchangeably, and because the civil times are subject to political changes. The problem (and the need to distinguish these concepts) becomes more evident for events in the future. Example (taken from my discussion here

John records in his calendar a reminder for some event at datetime
2019-Jul-27, 10:30:00, TZ=Chile/Santiago, (which has offset GMT+4,
hence it corresponds to UTC 2019-Jul-27 14:30:00). But some day
in the future, the country decides to change the TZ offset to GMT+5.

Another point to consider which I've not seen addressed in APIs is that even when time and time zone are given, there are at least two plausible meanings for e.g. "13:00:00EDT". It could be refer to something which is known to have occurred at 1pm local time, which was believed to have been Eastern Daylight Time, or something which is known to have occurred at the moment that Eastern time hit 13:00, which is believed to have occurred in a place observing Eastern Daylight Time. If one has many timestamps where the time-zone info may be wrong (e.g. digital camera files)...
–
supercatOct 29 '13 at 17:04

...knowing whether they reflect an accurate local time or global time may be important in determining how to correct them.
–
supercatOct 29 '13 at 17:04

1

Very useful. Just a small point: Chile/Santiago is GMT-4, I say this because the topic is about time conversion and related issues so it might be misleading to the readers (10:30 GMT+4 -> 6:30 UTC/GMT)
–
vizogJun 15 '14 at 8:27

Make clear architectural separation of concerns - to know exactly which tier intereacts with users, and has to change date-time for/from cannonical representation (UTC). Non-UTC date-time is presentation (follows users local timezone), UTC time is model (remains unique for back-end and mid tiers).

Also, decide what's your actual audience, what you don't have to serve and where do you draw the line. Don't touch exotic callendars unless you actually have important customers there and then consider separate user-facing server(s) just for that region.

If you can acquire and maintain user's location, use location for systematic date-time conversion (say .NET culture or a SQL table) but provide a way for end-user to choose overrides if date-time is critical for your users.

If there are historical audit obligations involved (like telling exactly when Jo in AZ paid a bill 2 yrs ago in September) then keep both UTC and local time for the record (your coversion tables will change in a course of time).

Define the time referential time zone for data that comes in bulk - like files, web services etc. Say East Coast company has data center in CA - you need to ask and know what they use as a standard instead of assuming one or the other.

Don't trust time-zone offsets embedded in textual representation of the date-time and don't accept to parse and follow them. Instead always request that time zone and/or refernece zone have to be explicitly defined. You can easily receive time with PST offset but the time is actually EST since that's cleints's reference time and records were just exported at a server which is in PST.

You need to know about the Olson tz database, which is available from ftp://elsie.nci.nih.gov/pubhttp://iana.org/time-zones/. It is updated multiple times per year to deal with the often last-minute changes in when (and whether) to switch between winter and summer (standard and daylight saving) time in different countries around the world. In 2009, the last release was 2009s; in 2010, it was 2010n; in 2011, it was 2011n; at the end of May 2012, the release was 2012c. Note that there is a set of code to manage the data and the actual time zone data itself, in two separate archives (tzcode20xxy.tar.gz and tzdata20xxy.tar.gz). Both code and data are in the public domain.

This is the source of time zone names such as America/Los_Angeles (and synonyms such as US/Pacific).

If you need to keep track of different zones, then you need the Olson database. As others have advised, you also want to store the data in a fixed format — UTC is normally the one chosen — along with a record of the time zone in which the data was generated. You may want to distinguish between the offset from UTC at the time and the time zone name; that can make a difference later. Also, knowing that it is currently 2010-03-28T23:47:00-07:00 (US/Pacific) may or may not help you with interpreting the value 2010-11-15T12:30 — which is presumably specified in PST (Pacific Standard Time) rather than PDT (Pacific Daylight Saving Time).

The standard C library interfaces are not dreadfully helpful with this sort of stuff.

The Olson data has moved, in part because A D Olson will be retiring soon, and in part because there was a (now dismissed) law suit against the maintainers for copyright infringement. The time zone database is now managed under the auspices of IANA, the Internet Assigned Numbers Authority, and there's a link on the front page to 'Time Zone Database'. The discussion mailing list is now tz@iana.org; the announcement list is tz-announce@iana.org.

The Olson database contains historical data as well, which makes it particularly useful for handling DST. E.g., it knows that a UTC value corresponding to March 31, 2000 in the U.S. is not in DST, but a UTC value corresponding to March 31, 2008 in the U.S. is.
–
Josh KelleyMar 29 '10 at 14:29

Where can one found information about parsing the Olson files? I want to compile it into a db table, but can't figure out how should I read the files
–
shealtielSep 7 '11 at 14:09

@gidireich: the main information is found with the data, and it is not easy to understand. The main documentation for it is in the zic.8 man page (or zic.8.txt). You'll need the tzcode2011i.tar.gz file rather than, or as well as, the tzdata2011i.tar.gz file to get this information.
–
Jonathan LefflerSep 7 '11 at 14:46

For display, the offset is perfect. If you want to make changes, the offset is still not enough. You have to also know the time zone because the new value may require a different offset.
–
Matt JohnsonJan 11 '13 at 1:30

Crossing the boundary of "computer time" and "people time" is a nightmare. The main one being that there is no sort of standard for the rules governing timezones and daylight saving times. Countries are free to change their timezone and DST rules at any time, and they do.

Some countries e.g. Israel, Brazil, decide each year when to have their daylight saving times, so it is impossible to know in advance when (if) DST will be in effect. Others have fixed(ish) rules as to when DST is in effect. Other countries do not use DST as all.

Timezones do not have to be full hour differences from GMT. Nepal is +5.45. There are even timezones that are +13. That means that:

Regarding Israel - this is half true. Though the time for the DST change is not a specific date in the Julian calendar - there is a rule based on the Hebrew calendar (there was a change in 2014). Anyway, the general idea is correct - this things might and do change once in a while
–
Yaron U.Apr 27 '14 at 6:31

While I haven't tried it, an approach to time zone adjustments I would find compelling would be as follows:

Store everything in UTC.

Create a table TZOffsets with three columns: RegionClassId, StartDateTime, and OffsetMinutes (int, in minutes).

In the table, store a list of dates and times when the local time changed, and by how much. The number of regions in the table and the number of dates would depend on what range of dates and areas of the world you need to support. Think of this as if it is "historical" date, even though the dates should include the future to some practical limit.

When you need to compute the local time of any UTC time, just do this:

You might want to cache this table in your app and use LINQ or some similar means to do the queries rather than hitting the database.

This data can be distilled from the public domain tz database [http://www.twinsun.com/tz/tz-link.htm].

Advantages and footnotes of this approach:

No rules are baked into code, you can adjust the offsets for new regions or date ranges readily.

You don't have to support every range of dates or regions, you can add them as needed.

Regions don't have to correspond directly to geopolitical boundaries, and to avoid duplication of rows (for instance, most states in the US handle DST the same way), you can have broad RegionClass entries that link in another table to more traditional lists of states, countries, etc.

For situations like the US where the start and end date of DST has changed over the past few years, this is pretty easy to deal with.

Since the StartDateTime field can store a time as well, the 2:00 AM standard change-over time is handled easily.

Not everywhere in the world uses a 1-hour DST. This handles those cases easily.

The data table is cross-platform and could be a separate open-source project that could be used by developers who use nearly any database platform or programming language.

This can be used for offsets that have nothing to do with time zones. For instance, the 1-second adjustments that happen from time to time to adjust for the Earth's rotation, historical adjustments to and within the Gregorian calendar, etc.

Since this is in a database table, standard report queries, etc. can take advantage of the data without a trip through business logic code.

This handles time zone offsets as well if you want it to, and can even account for special historical cases where a region is assigned to another time zone. All you need is an initial date that assigns a time zone offset to each region with a minimal start date. This would require creating at least one region for each time zone, but would allow you to ask interesting questions like: "What is the difference in local time between Yuma, Arizona and Seattle, Washington on February 2, 1989 at 5:00am?" (Just subtract one SUM() from the other).

Now, the only disadvantage of this approach or any other is that conversions from local time to GMT are not perfect, since any DST change that has a negative offset to the clock repeats a given local time. No easy way to deal with that one, I'm afraid, which is one reason storing local times is bad news in the first place.

Your database idea has already been implemented as the Olson Database. While daylight savings time does create an overlap specifying the daylight specific timezone can help resolve the issue. 2:15 EST and 2:15 EDT are different times. As noted in other posts specifying the offset also resolves the ambiguity.
–
BillThorAug 3 '10 at 23:04

3

The big problem with keeping your own database is keeping it updated. Olson and Windows are maintained for you. I've had more than one case of having bad DST transitions because tables were out of date. Ripping them out entirely and relying on a framework or OS level database is much more reliable.
–
Matt JohnsonJan 11 '13 at 1:28

If your design can accommodate it, avoid local time conversion all together!

I know to some this might sound insane but think about UX: users process near, relative dates (today, yesterday, next Monday) faster than absolute dates (2010.09.17, Friday Sept 17) on glance. And when you think about it more, the accuracy of timezones (and DST) is more important the closer the date is to now(), so if you can express dates/datetimes in a relative format for +/- 1 or 2 weeks, the rest of the dates can be UTC and it wont matter too much to 95% of users.

This way you can store all dates in UTC and do the relative comparisons in UTC and simply show the user UTC dates outside of your Relative Date Threshold.

This can also apply to user input too (but generally in a more limited fashion). Selecting from a drop down that only has { Yesterday, Today, Tomorrow, Next Monday, Next Thursday } is so much simpler and easier for the user than a date picker. Date pickers are some of the most pain inducing components of form filling. Of course this will not work for all cases but you can see that it only takes a little clever design to make it very powerful.

The DateTimeZone class in PHP > 5.2 is already based on the Olson db which others mention, so if you are doing timezone conversions in PHP and not in the DB, you are exempt of working with (the hard-to-understand) Olson files.

However, PHP is not updated as frequently as the Olson DB, so just using PHPs time zone conversions may leave you with outdated DST information, and influence the correctness of your data. While this is not expected to happen frequently, it may happen, and will happen if you have large base of users worldwide.

To cope with the above issue, exist a pecl package, that all it's funcionality is to update PHP's TimeZone data. Install this package as frequently as it is updated, and you are at a very good standpoint.
I'm not sure if this packages updates follow Olson updates exactly, but it seems to be updated in a frequency which is at least very close to Olson updates

I have hit this on two types of systems, “shift planning systems (e.g. factory workers)” and “gas depend management systems)…

23 and 25 hour long days are a pain to cope with, so are 8hr shifts that take 7hr or 9hr. The problem is you will find that each customers, or even department of the customer have different rules they have created (often without documenting) on what they do in these special cases.

Some questions are best not asked of the customer’s until after they have paid for your “off the shelf” software. It is very rare to find a customer that thinks about this type of issue up front when buying software.

I think in all cases you should record time in UTC and convert to/from local time before storing the date/time. However even know which take a given time is in can be hard with Daylight saving and time zones.

My girlfriend, a nurse, works nights and they have to write the timezone during the DST switchover. For instance, 2AM CST vs 2AM CDT
–
Joe PhilllipsAug 5 '10 at 2:48

Yes, this is common: when you compute a (civil) daily average, you have to cope with days of 23, 24, or 25 hours. As a consequence, when you compute add 1 day it is not add 24 hours! Second, don't try to build your own timezone code ; only use systems API. And don't forget to update each deployed system in order to get the up-to-date timezone database.
–
AlexOct 9 '13 at 9:44

I recently had a problem in a web application where on an Ajax post-back the datetime coming back to my server-side code was different from the datetime served out.

It most likely had to do with my JavaScript code on the client that built up the date for posting back to the client as string, because JavaScript was adjusting for time zone and daylight savings, and in some browsers the calculation for when to apply daylight savings seemed to be different than in others.

In the end I opted to remove date and time calculations on the client entirely, and posted back to my server on an integer key which then got translated to date time on the server, to allow for consistent transformations.

My learning from this:
Do not use JavaScript date and time calculations in web applications unless you ABSOLUTELY have to.

Javascript runs at the client machine, not at the server machine. Javascript's base datetime is the client's datetime, not the server's datetime.
–
BalusCMar 29 '10 at 11:39

Hi BalusC. I understand this (from my original post: "javacode script on the client that built up the date..."). My issue was that certain client machine processed DST one way, and some others another way. Therefore I moved all of that processing server-side to prevent the browser weirdness
–
JoonMar 31 '10 at 8:28

@Joon—and there are bugs in javascript implementations that you can't detect or allow for. So yes, don't ever use client–side ECMAScript date calculations for things that matter (though you can do a pretty good job otherwise).
–
RobGOct 16 '14 at 2:19

This PHP method returns an associative array containing some 'major' timezones (like CEST), which on their own contain more specific 'geographic' timezones (like Europe/Amsterdam).

If you're using these timezones and their offset/DST information, it's extremely important to realize the following:

It seems like all different offset/DST configurations (including historical configurations) of each timezone are included!

For example, Europe/Amsterdam can be found six times in the output of this function. Two occurrences (offset 1172/4772) are for the Amsterdam time used until 1937; two (1200/4800) are for the time that was used between 1937 and 1940; and two (3600/4800) are for the time used since 1940.

Therefore, you cannot rely on the offset/DST information returned by this function as being currently correct/in use!

If you want to know the current offset/DST of a certain timezone, you'll have to do something like this:

If you happen to maintain database systems that are running with DST active, check carefully whether they need to be shut down during the transition in fall. Mandy DBS (or other systems as well) don't like passing the same point in (local) time twice, which is exactly what happens when you turn back the clock in fall. SAP has solved this with a (IMHO really neat) workaround - instead of turning back the clock, they just let the internal clock run at half the usual speed for two hours...

Business rules should always work on civil time (unless there's legislation that says otherwise). Be aware that civil time is a mess, but it's what people use so it's what is important.

Internally, keep timestamps in something like civil-time-seconds-from-epoch. The epoch doesn't matter particularly (I favour the Unix epoch) but it does make things easier than the alternative. Pretend that leap-seconds don't exist unless you're doing something that really needs them (e.g., satellite tracking). The mapping between timestamps and displayed time is the only point where DST rules should be applied; the rules change frequently (on a global level, several times a year; blame politicians) so you should make sure that you do not hard-code the mapping. Olson's TZ database is invaluable.

The problem here is that civil time (aka calendar time) does not properly represent a single moment in time. If you take the seconds-from-epoch approach, are you really going to account for every moment that was skipped over or rewound for daylight savings transitions? Probably not. Epoch basis only works against UTC or some other fixed, instantaneous-time reference.
–
Matt JohnsonJan 11 '13 at 1:21

The local time offset will allow you to recreate the original "wall time" of the event. If you do not store this additional field, this data is lost.
–
nogridbagApr 16 '12 at 18:33

Yes, except UTC and GMT offsets are inverse. For example UTC-0700 is the same as GMT+0700. Really, everything digital should be using UTC these days.
–
Matt JohnsonSep 13 '12 at 19:36

1

@Matt: are you sure about UTC and GMT offsets being inverse? where can I read up on that?
–
ZalumonFeb 16 '13 at 14:22

Actually, I think I was incorrect before. GMT and UTC aren't inverted themselves. It's that there are implementations that show them in inverse, such as the IANA/Olson/TZDB database. See here. Another area you see offsets in inverse is in javascript's getTimezoneOffset() function.
–
Matt JohnsonFeb 16 '13 at 17:49

@MattJohnson: Those are hacks and should be abolished.
–
Alix AxelFeb 16 '13 at 22:55

They can throw exceptions when a certain date time does not exist due to DST. Instead, build your own methods for creating such dates. In them, catch any exceptions that occur due to DST, and adjust the time is needed with the transition offset. DST may occur on different dates and at different hours (even at midnight for Brazil) according to the timezone.

On server-side, store times in UTC format so that all date/time values in database are in a single standard regardless of location of users, servers, timezones or DST.

On the UI layer or in emails sent out to user, you need to show times according to user. For that matter, you need to have user's timezone offset so that you can add this offset to your database's UTC value which will result in user's local time. You can either take user's timezone offset when they are signing up or you can auto-detect them in web and mobile platforms. For websites, JavaScript's function getTimezoneOffset() method is a standard since version 1.0 and compatible with all browsers. (Ref: http://www.w3schools.com/jsref/jsref_getTimezoneOffset.asp)

In dealing with databases (in particular MySQL, but this applies to most databases), I found it hard to store UTC.

Databases usually work with server datetime by default (that is, CURRENT_TIMESTAMP).

You may not be able to change the server timezone.

Even if you are able to change the timezone, you may have third-party code that expects server timezone to be local.

I found it easier to just store server datetime in the database, then let the database convert the stored datetime back to UTC (that is, UNIX_TIMESTAMP()) in the SQL statements. After that you can use the datetime as UTC in your code.

If you have 100% control over the server and all code, it's probably better to change server timezone to UTC.

Local time (the server time) can be ambiguous during "fall-back" style daylight savings transitions. It's not reliable to use as you described. There may be more than one UTC time that the server's local time could be representing.
–
Matt JohnsonJan 11 '13 at 1:13

Local time is perfectly fine if the server is located in a sane time zone, i.e. one that doesn't do DST.
–
sayapApr 2 '13 at 10:06