Showing posts with label timezone. Show all posts
Showing posts with label timezone. Show all posts

Friday, February 8, 2013

Database handling for TimeZones

Today I’ve been working with Postgresql and MySQL trying to figure out how they handle date-time values and timezones. This is actually quite tricky, so I wanted to write it down for later. First, both databases have two different types of columns:
  • One that stores the date-time without any time zone information
  • One that stores the date-time with time zone information
For Postgresql, these are:
  • timestamp without time zone
  • timestamp with time zone
  • respectively.
    For MySQL, these are:
    • datetime
    • timestamp
    There are a number of things to consider when dealing with date-time values:
    • What if the timezone of the server changes?
    • What if the server moves physical locations thereby indicating a new time zone?
    • What if the timezone of the database server changes (different than the timezone of the server)?
    • How does the JDBC driver handle timezones?
    • How does the database handle timezones?
    To figure all of this out, it is important to understand how the date-time value goes from the application, to the database, out of the database and back to the application (for inserts and later selects). Here is how this works for values without timezone information:
    Insert
    1. Java creates a java.sql.Timestamp instance. This is stored as UTC
    2. Java sends this value to the JDBC driver in UTC
    3. The JDBC driver sends the value to the database in UTC
    4. The database converts the date-time from UTC to its current timezone setting
    5. The database inserts the value into the column in the current timezone (NOT UTC)
    Select
    1. The database selects the value from the column in the system timezone
    2. The database converts the value to UTC
    3. The database sends the UTC value to the JDBC driver
    4. The JDBC driver creates a new java.sql.Timestamp instance with the UTC value
    For this scenario, you will run into major issues if the the server or database timezone changes between Insert #5 and Select #1. In this case, the value will not be correct. The only way to make things work correctly for this setup is to ensure no timezone settings ever change or to set all timezones to UTC for everything.
    For the types that store timezone information, here is how the data is passed around:
    Insert
    1. Java creates a java.sql.Timestamp instance. This is stored as UTC
    2. Java sends this value to the JDBC driver in UTC
    3. The JDBC driver sends the value to the database in UTC
    4. The database calculates the offset between the current timezone and UTC
    5. The database inserts the value as UTC into the column along with the offset it calculated
    Select
    1. The database selects the value from the column in UTC along with the offset
    2. The database sends the UTC value to the JDBC driver
    3. The JDBC driver creates a new java.sql.Timestamp instance with the UTC value
    Since the JDBC driver only handles UTC values, there is no potential for data mangling here since everything is stored in UTC inside the database. Although the database stores the timezone information along with the date-time value in UTC, it isn’t ever used because the JDBC driver doesn’t care what the original timezone was and ignores that value.

Thursday, December 20, 2012

Should dateTime elements include time zone information in SOAP messages?

Given the following sentences from the w3c schema documentation:
"Local" or untimezoned times are presumed to be the time in the timezone of some unspecified locality as prescribed by the appropriate legal authority;
and
When a timezone is added to a UTC dateTime, the result is the date and time "in that timezone".
it does not sound like there is a definitive answer to this. I would assume that it is the usual ambiguity: Both versions are principally valid, and the question of what version to use depends on the configuration/behavior/expectations of the system one is interfacing with.
And even if there where a definitive answer, I would definitely not rely on it, but rather expect that every other web service and library had its own way of dealing with this :/

 http://stackoverflow.com/questions/1642636/should-datetime-elements-include-time-zone-information-in-soap-messages

xml schema Timezone

I think that your interpretation might be off; I can't see how you ended up with GMT+13:00 from 13:00:00Z.
The XSD spec gives the following example:
2002-10-10T12:00:00+05:00 is 2002-10-10T07:00:00Z 
2002-10-10T00:00:00+05:00 is 2002-10-09T19:00:00Z
A nonnegative duration means that the time zone is ahead; negative is behind.
Assuming that the timestamp was taken at midnight (12:00AM), and it matched 13:00:00Z, then you could offset this either ahead or behind:
Behind: 2012-02-04T00:00:00-13:00 is 2012-02-04T13:00:00Z
Ahead:  2012-02-05T00:00:00+11:00 is 2012-02-04T13:00:00Z
The only one valid is the ahead (there is no -13); As to what is in that time zone, take a look here.

http://stackoverflow.com/questions/9950406/xml-schema-timezone

Monday, December 17, 2012

Java.util.Calendar - milliseconds since Jan 1, 1970

The dates you print from Calendar are local to your timezone, whereas the epoch is defined to be midnight of 1970-01-01 in UTC. So if you live in a timezone west of UTC, then your date will show up as 1969-12-31, even though (in UTC) it's still 1970-01-01.

        Calendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(1970, 0, 1, 0, 0, 0);
        System.out.println(cal.getTimeInMillis());

http://stackoverflow.com/questions/263376/java-util-calendar-milliseconds-since-jan-1-1970

Monday, December 10, 2012

Java Calendar timezone converting to GMT problem

Just create a new Calendar in GMT, set the time in that calendar to the same as the original calendar, and you're done:
gmtCalendar.setTime(userCalendar.getTime());
That should be fine, as the getTime() call returns the instant in time (i.e. a java.util.Date with no associated time zone).
As ever though, if you're doing any significant amount of date/time work in Java you should strongly consider using Joda Time instead.

http://stackoverflow.com/questions/6790664/java-calendar-timezone-converting-to-gmt-problem

Friday, December 7, 2012

Time Zones and Daylight Saving Time in Java

You might think this would be simple but I actually spent a fair amount of time last week tracking down some confusing bugs. The problem is that the official documentation is pretty sparse and if you Google for support you will find many answers that are confused or flat-out wrong. Hopefully this will provide the straight dope.
The key Java classes are as follows:

java.util.TimeZone

This is an abstract class that contains the definition of a time zone, including that zone’s rules for Daylight Saving Time (or what Europeans refer to as “Summer Time.”) Any code that needs to explicitly deal with time zones should use a TimeZone object.
The best way to get a TimeZone is to call the static method TimeZone.getTimeZone() and pass it the standard “Olson name” such as “America/New_York” or “Pacific/Honolulu”.
This link lists all the standard time zone names.
You can call TimeZone.getTimeZone(“GMT”) or TimeZone.getTimeZone(“GMT-5″). None of these GMT-based TimeZone objects support Daylight Saving Time. (Note: “GMT” and “UTC” mean practically the same thing. GMT is defined in terms of astronomical observations and UTC is used for setting atomic clocks. For most practical purposes they can be treated as identical.)
You can also use common abbreviations e.g. TimeZone.getTimeZone(“EST”) instead of TimeZone.getTimeZone(“America/New_York”). This is strongly discouraged. Depending on circumstances you might get the wrong Daylight Saving Time behavior or even the totally wrong time zone, since the same 3-letter abbreviations are used around the world for different time zones.
When displaying a time zone to the user you should probably call TimeZone.getDisplayName(). Depending on the parameters you pass this will return a user-friendly value like “Eastern Standard Time”, “EST”, “Eastern Daylight Time” or “EDT”.

java.util.Calendar

This is an abstract class which serves as a wrapper around two independent values:
  • A time, stored as the number of milliseconds since January 1, 1970 00:000:00 GMT.
  • A TimeZone object which indicates how the time should be displayed.
(This is an oversimplification. The actual implementation is a a bit more complicated, but this is close enough as long as you are not actually digging into the source code.)
Note that the time offset is always supposed to be in GMT. If you see code samples that make a different assumption (and there are many out there on the web) ignore them.
Time zone conversions are simple: if you call setTimeZone() on a Calendar object you get the exact same time but displayed in the new time zone.
A more complex problem occurs when you get a time string from a user in a different time zone. If you parse the string “05-01-2012 08:35 AM” then the parser will generally give you a Calendar object for 8:35 AM in the computer’s default time zone.
If this is wrong then you will need to change the time offset to convert it to the correct time. If the time string was supposed to be in GMT then you can use the folowing code to convert it.

public static Calendar convertToGmt(Calendar c) {
    java.util.Date date = c.getTime();
    TimeZone tz = c.getTimeZone();
    long timeInMilliseconds = date.getTime();
    int offsetFromUTC = tz.getOffset(timeInMilliseconds);
    Calendar gmtCal = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
    gmtCal.setTime(date);
    gmtCal.add(Calendar.MILLISECOND, offsetFromUTC);
    return gmtCal;
}

If it was supposed to be in a different time zone then you can call TimeZone.getOffset() for both time zones. The difference between the two values will give you the number of milliseconds that you need to add to do the conversion.
This code provides an alternate way to convert between arbitrary time zones.

public static Calendar convertToNewTimeZone(Calendar calendar, TimeZone timezone) {
    Calendar newCal = new GregorianCalendar(timezone);
    newCal.setLenient(false);
    boolean am = newCal.get(Calendar.AM_PM) == Calendar.AM;
    newCal.set(Calendar.YEAR, calendar.get(Calendar.YEAR));
    newCal.set(Calendar.MONTH, calendar.get(Calendar.MONTH));
    newCal.set(Calendar.DATE, calendar.get(Calendar.DATE));
    newCal.set(Calendar.HOUR, calendar.get(Calendar.HOUR));
    newCal.set(Calendar.MINUTE, calendar.get(Calendar.MINUTE));
    newCal.set(Calendar.SECOND, calendar.get(Calendar.SECOND));
    newCal.set(Calendar.MILLISECOND, calendar.get(Calendar.MILLISECOND));
    boolean ampm = calendar.get(Calendar.AM_PM) == Calendar.PM;
    if (am && ampm) { // cal = 0 but we want 1
        newCal.roll(Calendar.AM_PM, 1);
    } else if (!am && !ampm) { //cal = 1 but we want 0
        newCal.roll(Calendar.AM_PM, -1);
    }
    return newCal;
}

Once again, this gives you a Calendar object with the same wall-clock time in a different time zone, as opposed to getting the same actual time in a different time zone.

ISO 8601

To avoid such problems when sending dates and times between different time zones you can use the ISO 8601 formats commonly used in XML documents. These formats allow an optional trailing time zone indicator e.g.
2012-05-01T08:35:01.123Z
2012-05-01T08:35:01.123-05:00
A “Z” code indicates that the time is GMT. A “-05:00″ indicates a time zone that is 5 hours behind GMT. In the U.S. this could mean either “Eastern Standard Time” or “Central Daylight Time”.
Most standard XML libraries can handle these formats.
In the “-05:00″ example above the parser will return a Calendar subclass whose TimeZone object is “GMT-5″, not “America/New_York” or “America/Chicago”. You have the correct time but you don’t really know which official time zone it is.
The time zone indicator is optional. If the document contains
2012-05-01T08:35:01.123
that will be interpreted as being in the receiving computer’s default time zone.

java.util.Date

This is a wrapper around a count of milliseconds since midnight January 1, 1970. There is no associated time zone.
According to the documentation the millisecond count should always be in GMT, but this is often ignored. You will find many code samples on the web that attempt to deal with time zones by adding or subtracting hours. This is NOT recommended.
If you need to deal with time zones you should use a Calendar object.
The Date class has methods like getHours() and getMinutes() which are all deprecated. If you use them they will return the value in the computer’s default time zone. Date.toString() will also display in the computer’s default time zone.

java.sql.Date

This is intended to represent a SQL DATE field. The Java implementation is a simple wrapper around java.util.Date which makes sure that the time part is always set to midnight in your computer’s default time zone.
What is actually stored in the database depends on the implementation but can be assumed to consist of a year, month and day in some format.

java.sql.Time

This is intended to represent a SQL TIME field. The Java implementation is a thin wrapper around java.util.Date which makes sure that the date part is always set to January 1, 1970.
What is actually stored in the database depends on the implementation but can be assumed to consist of either an offset from midnight or a combination of hour, minutes and seconds in some format.
There is no support for time zones built in. If time zones are important the application will have to keep track of them separately.

java.sql.TimeStamp

This intended to represent a SQL TIMESTAMP field. The Java implementation is similar to java.util.Date in that it contains an offset from a fixed starting time, but it is much higher precision, supporting fractions of a microsecond instead of milliseconds.
What is actually stored in the database depends on the implementation but is usually some sort of offset from a starting date.
TimeStamp is similar to java.util.Date in that
  • It does not contain a TimeZone.
  • The internal offset is supposed to be in GMT and bad things can happen if it is not.
  • It is typically displayed in the computer’s default time zone.
Usually you create a TimeStamp from a java.util.Date object. (If you are starting with a Calendar object just call getTime() on it.) So basically you are writing out the millisecond offset in the GMT time zone. If you started with a Calendar its original time zone is lost.
When you read a TimeStamp from the database you are getting back the time with no time zone information. Calling getDate() will return it as a java.util.Date object. If you want it in a time zone other than your computer’s default time zone you can do something like this:

Calendar cal = new GregorianCalendar(desiredTimeZone);
cal.setTime(ts.getDate);


http://bugfox.net/blog/2012/05/04/time-zones-and-daylight-saving-time-in-java/

Converting EST to EDT or vice versa in Java

How to retrieve TimeZone from java.util.Date instance?
There's no such thing. A Date just represents a number of milliseconds since the Unix epoch, which was midnight on January 1st 1970 UTC. It's not associated with a particular calendar system or time zone. To put it another way, if a friend and I are on the phone together (with a zero latency ;) and I click my fingers, we would both agree on the Date at which that click too place - even if I'm using the Gregorian calendar and he's using the Julian calendar, and even if I'm in London and he's in New York. It's the same instant in time.
How to know whether Daylight savings is applicable?, I suppose I can know it by doing timeZone.getDSTSavings, but problem I am facing is that even if I make my system's date as Feb 1 2012, still I am getting the value as positive (I guess 3600000)
Ideally, use Joda Time instead of java.util.Date/Calendar/TimeZone, but within TimeZone you can use TimeZone.getOffset(long) to find the offset from UTC, or TimeZone.inDaylightTime(Date) to just give you a yes/no answer.
How to convert EST time to EDT or vice versa?
Usually that's an invalid question - because at any one instance in time, either EST or EDT applies. You normally convert from one time zone to another, and "EDT" and "EST" aren't different time zones - they're different offsets within the same time zone. The fact that you're asking for this suggests that you may be modelling your data incorrectly to start with (which is unfortunately easy to do with date/time values). Please give us more information and we may be able to help you more.

http://stackoverflow.com/questions/9827139/converting-est-to-edt-or-vice-versa-in-java

Thursday, December 6, 2012

How can I get the current date and time in UTC or GMT in Java?

java.util.Date is always in UTC. What makes you think it's in local time? I suspect the problem is that you're displaying it via an instance of Calendar which uses the local timezone, or possibly using Date.toString() which also uses the local timezone.
If this isn't the problem, please post some sample code.
I would, however, recommend that you use Joda Time anyway, which offers a much clearer API.

http://stackoverflow.com/questions/308683/how-can-i-get-the-current-date-and-time-in-utc-or-gmt-in-java

Wednesday, December 5, 2012

Insert UTC date in Oracle database with Java and Spring

Neither java.util.Date nor Oracle Date stores timezone information. In your case Jdbc driver converts your date using the JVM timezone. You can use one of the following options:
  • If you are using PreparedStatement, you can use setDate(int parameterIndex, Date x, Calendar cal) method to specify Calendar in UTC timezone.
  • For Spring jdbcTemplate instead of inserting Date object, insert Calendar with UTC timezone
  • TimeZone.setDefault(TimeZone.getTimeZone("GMT")) could be set on JVM lvl
  • Use -Duser.timezone=GMT on JVM startup
http://stackoverflow.com/questions/12563553/insert-utc-date-in-oracle-database-with-java-and-spring

What is the default timezone in java.util.Date?

The date itself doesn't have any time zone. Its toString() method uses the current default time zone to return a String representing this date:
Date date = new Date();

System.out.println(TimeZone.getDefault());
System.out.println(date);

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

System.out.println(TimeZone.getDefault());
System.out.println(date);
Executing the above code on my machine leads to the following output:
sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]
Fri Jul 06 09:24:45 CEST 2012
sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]
Fri Jul 06 07:24:45 UTC 2012
 
http://stackoverflow.com/questions/11337557/what-is-the-default-timezone-in-java-util-date 

Monday, August 6, 2012

How to get the current date and time of your timezone in Java?

As Jon Skeet already said, java.util.Date does not have a time zone. A Date object represents a number of milliseconds since January 1, 1970, 12:00 AM, UTC. It does not contain time zone information.
When you format a Date object into a string, for example by using SimpleDateFormat, then you can set the time zone on the DateFormat object to let it know in which time zone you want to display the date and time:
Date date = new Date();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// Use Madrid's time zone to format the date in
df.setTimeZone(TimeZone.getTimeZone("Europe/Madrid"));
System.out.println("Date and time in Madrid: " + df.format(date));
If you want the local time zone of the computer that your program is running on, use:
df.setTimeZone(TimeZone.getDefault());
http://stackoverflow.com/questions/1305350/how-to-get-the-current-date-and-time-of-your-timezone-in-java 

Friday, August 3, 2012

SimpleDateFormat and Time Zones

Java's SimpleDateFormat does not handle time zones properly if you only pass it a date object. For example, if your server is in the "America/Los_Angeles" time zone and you try to get the time in New York, you'd think this should work:
TimeZone tz = TimeZone.getTimeZone("America/New_York");
Calendar cal = Calendar.getInstance(tz); //returns a calendar set to the local time in New York
SimpleDateFormat format = new SimpleDateFormat("hh:mma z");
format.format(cal.getTime());
However, this returns a String like "4:00pm PDT", when it should return "7:00pm EDT". The reason for this (I think) is that the Date object returned by cal.getTime() does not convey any time zone information. The solution is to explicitly set the calendar object into the SimpleDateFormat like this:
TimeZone tz = TimeZone.getTimeZone("America/New_York");
Calendar cal = Calendar.getInstance(tz); //returns a calendar set to the local time in New York
SimpleDateFormat format = new SimpleDateFormat("hh:mma z");
format.setCalendar(cal);  //explicitly set the calendar into the date formatter
format.format(cal.getTime());
Note that if your application uses a static SimpleDateFormat, you should clone it before you call setCalendar() so that other uses of the format are not set to that explicit time zone:
SimpleDateFormat myFormat = (SimpleDateFormat)FORMAT.clone();
myFormat.setCalendar(cal);
myFormat.format(cal.getTime());
 
https://www.codemagi.com/blog/post/192 

Wednesday, August 1, 2012

What is the difference between EDT and EST?

EDT is Eastern Daylight Time
EST is Eastern Standard Time.

The difference between EDT and EST is that EDT is used in the summer in many states, provinces and territories in the US and Canada, including: Quebec, Nunavut, Ontario, Conneticut, Florida, Michigan, New Jersey and many more.
EST is used in winter in these places.

Hope this is helpful!
If you need anymore information about this, you can go to this website which I recommend. It is contained in the additional resources below.
http://wiki.answers.com/Q/What_is_the_difference_between_EDT_and_EST

Force Java timezone as GMT/UTC

The OP answered this question to change the default timezone for a single instance of a running JVM, set the user.timezone system property:
java -Duser.timezone=GMT ... <main-class>
If you need to set specific time zones when retrieving Date/Time/Timestamp objects from a database ResultSet, use the second form of the getXXX methods that takes a Calendar object:
Calendar tzCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
ResultSet rs = ...;
while (rs.next()) {
    Date dateValue = rs.getDate("DateColumn", tzCal);
    // Other fields and calculations
}
Or, setting the date in a PreparedStatement:
Calendar tzCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
PreparedStatement ps = conn.createPreparedStatement("update ...");
ps.setDate("DateColumn", dateValue, tzCal);
// Other assignments
ps.executeUpdate();
These will ensure that the value stored in the database is consistent when the database column does not keep timezone information.
The java.util.Date and java.sql.Date classes store the actual time (milliseconds) in UTC. To format these on output to another timezone, use SimpleDateFormat. You can also associate a timezone with the value using a Calendar object:
TimeZone tz = TimeZone.getTimeZone("");
//...
Date dateValue = rs.getDate("DateColumn");
Calendar calValue = Calendar.getInstance(tz);
calValue.setTime(dateValue);
 
http://stackoverflow.com/questions/2627992/force-java-timezone-as-gmt-utc 

JPA/Hibernate store date in UTC time zone

To the best of my knowledge, you need to put your entire java app in UTC timezone (so that hibernate will store dates in UTC), and you'll need to convert to whatever timezone desired when you display stuff (at least we do it this way).
At startup, we do:
TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));
and set the desired timezone to the DateFormat:
fmt.setTimeZone(TimeZone.getTimeZone("Europe/Budapest"))
Hope this helps,
cheers,
mitch

http://stackoverflow.com/questions/508019/jpa-hibernate-store-date-in-utc-time-zone

How to set a JVM Timezone Properly

You can pass the JVM this param -Duser.timezone, for example -Duser.timezone="Europe/Sofia" and this should do the trick. Setting the environment variable TZ might also help.

http://stackoverflow.com/questions/2493749/how-to-set-a-jvm-timezone-properly

Tuesday, July 31, 2012

Timezone Offsets With Sql Server - Revisited

This week-end I played a little bit with timezones and two new SQL Server 2008 functions:

  • TODATETIMEOFFSET()
  • SWITCHOFFSET()

It reminded me how I was handling timezones on my websites and I decided to write about it.

Table of Contents



Introduction


Hi,

Timezone is always a challenge for programmers and webmasters. It is somewhat difficult for everyone on a website to see the content according to their own timezone. If we didn't care about your timezone, you would be able to read articles in the future, or think that an item you want on eBay is already sold.

So everyone must be synchronized. And this synchronization is possible with UTC. Every region in the world have a time relative to UTC. The difference of time between UTC and your region is called an offset. So when we bring everyone on the site back to a zero offset, all the users are synchronizedin the present moment, not according to their time of sunshine!

So how we do it? As far as I know, programmers always implemented a solution in the application layer, not the database layer. With SQL Server 2008 however, we have more options...

A Solution - Application Layer


The trick is, when you have a website with users from all around the world, you put all dates and times converted to UTC in the database.

Here's how I do it:

  • When collecting a date from the user, you convert it to UTC and insert it in the database
  • When getting the current date from SQL Server, you use GETUTCDATE() whenever you would use GETDATE()
  • When displaying a date to the user, you select it from the database, then in your application you convert it relatively to the user's timezone

So if you have a couple of DateTime fields here and there, they should all contains UTC dates in the database. There's one catch with this solution, you have to know the user's timezone whenever you need to display a date, and it's not an easy task.

Well isn't it the root of the problem, knowing your users timezone? Yes but there's no reliable way to determine it without asking them. JavaScript is of little help if I remember correctly and is not reliable. The way I do it, I ask the user's timezone on registration. Another solution would be to put a drop down on pages to change the current timezone and remember the selection in a cookie..

Another (new) Solution - Database Layer


Well, with SQL Server 2008 we have an alternative solution, but it's basically the same thing as above:

  • Every dates you insert in the database should be in UTC
  • When getting the current date from SQL Server you use GETUTCDATE()
  • But when the dates are arriving to the application layer from the database layer, they already should be in the user's timezone (This is the difference)

This means the work is offloaded from the application layer to the database layer. Let's look at some Transact-SQL to see how we do it:

The new function in Transact-SQL to offset a DateTime value is 

SWITCHOFFSET(Date, OffsetAmount)

Where...

  • Date is the date we want to offset (or convert) (expressed in DATETIMEOFFSET)
  • OffsetAmount is how much we want to offset Date to reach a specific timezone

For example, if Date is UTC, and we want to convert it to GMT-05:00 (Eastern), it would be great if we could write:

SELECT SWITCHOFFSET(DateColumn, '-05:00')

And the result of the SELECT would be a date. But that's not quite it. SWITCHOFFSET has been designed to offset any date, not just UTC dates. So the first parameter does not accept a DateTime type but rather a DATETIMEOFFSET structure.

Fortunately, converting a DateTime to a DATETIMEOFFSET is pretty easy. Suppose your DateTime values in a column is in UTC, you would convert the column with:

TODATETIMEOFFSET(DateColumn, '+00:00')

We give a zero offset because we want to stay in UTC. The function returns what we need. Now let's try to put it all together:

SELECT SWITCHOFFSET(TODATETIMEOFFSET(DateColumn, '+00:00'), '-05:00')

We have now converted a UTC date in the database to a GMT-05:00 (Eastern) date! Notice that since the beginning of this article we're working with UTC dates. But keep in mind that you can convert any timezone to any timezone.

Analysis


Moving the date offset calculation from the application layer to the database layer has no notable advantages in my opinion. Depending on the size of the application, it may be simpler. To aid in your decision, I dressed up a list of advantages and disadvantages of doing so. Feel free to post your comments and suggestions!

Adjusting the timezone in the application layer


The good

  • Your SQL queries are simpler
  • You are able to convert any dates from any source, not just those coming from the database
  • If you change your data provider, you're still in business
  • Layers are less coupled

The bad

  • The user's timezone have to be known by the application layer. But retrieving it from the database and storing it in the session is pretty flexible and easy.
  • You have to convert the dates before displaying them to the user. There could be many places where you can forget to do so... Where you do it? Presentation layer? Controllers? You have to pass the target timezone around, etc

Adjusting the timezone in the database layer


The good

  • Everything that comes out of your database (stored procedures, functions, queries, etc.) is already in the proper timezone
  • The application layer is a little less complex

The bad

  • There is not translation in LINQ to SQL for SWITCHOFFSET
  • The user's timezone have to be known by the database layer (Not a real problem if we store the user's timezone in the database)
  • If the timezone is not stored in the database, the application layer has to constantly pass down this information, creating complexity and maintainability problems
  • If the application layer displays information to the user that's not in the database, the application still have to convert the dates to the user's timezone and we now have an hybrid model we have to maintain (bad)
  • Your SQL Queries could be a little more complex. For example, you'll consistently have to join the table where the user's timezone is stored.

Conclusion


Just because now you can doesn't mean you should calculate your timezones in the database layer, you have to take what fits your needs. You shouldn't take performance as a decision factor but rather maintainability and flexibility. And after weighting the two, I will still do it in the application layer...

You now have an overview of what it takes to create a website "time friendly", and how to do it!

I'm putting time and effort on this blog and having you here is my reward. Make me feel better and better everyday by spreading the love with the buttons below! Also, don't hesitate to leave a comment. Thanks for reading!


http://blog.mikecouturier.com/2009/12/timezone-offsets-with-sql-server.html

Wednesday, July 25, 2012

What does the 'Z' mean in Unix timestamp '120314170138Z'?

Yes. 'Z' stands for Zulu time, which is also GMT and UTC.
From http://en.wikipedia.org/wiki/Coordinated_Universal_Time:
The UTC time zone is sometimes denoted by the letter Z—a reference to the equivalent nautical time zone (GMT), which has been denoted by a Z since about 1950. The letter also refers to the "zone description" of zero hours, which has been used since 1920 (see time zone history). Since the NATO phonetic alphabet and amateur radio word for Z is "Zulu", UTC is sometimes known as Zulu time.
Technically, because the definition of nautical time zones is based on longitudinal position, the Z time is not exactly identical to the actual GMT time 'zone'. However, since it is primarily used as a reference time, it doesn't matter what area of Earth it applies to as long as everyone uses the same reference.
From wikipedia again, http://en.wikipedia.org/wiki/Nautical_time:
Around 1950, a letter suffix was added to the zone description, assigning Z to the zero zone, and A–M (except J) to the east and N–Y to the west (J may be assigned to local time in non-nautical applications; zones M and Y have the same clock time but differ by 24 hours: a full day). These were to be vocalized using a phonetic alphabet which pronounces the letter Z as Zulu, leading sometimes to the use of the term "Zulu Time". The Greenwich time zone runs from 7.5°W to 7.5°E longitude, while zone A runs from 7.5°E to 22.5°E longitude, etc.
  http://stackoverflow.com/questions/9706688/what-does-the-z-mean-in-unix-timestamp-120314170138z

Tuesday, July 10, 2012

Java - How to set timezone of a java.util.Date

Use DateFormat. For example,
    SimpleDateFormat isoFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    isoFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
    Date date = isoFormat.parse("2010-05-23T09:01:02");
 
Be aware that java.util.Date objects do not contain any timezone information by themselves - you cannot set the timezone on a Date object. The only thing that a Date object contains is a number of milliseconds since the "epoch" - 1 January 1970, 00:00:00 UTC.
As ZZ Coder shows, you set the timezone on the DateFormat object, to tell it in which timezone you want to display the date and time.

http://stackoverflow.com/questions/2891361/java-how-to-set-timezone-of-a-java-util-date