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
• A Solution - Application Layer
• Another (new) Solution - Database Layer
• Analysis
• Adjusting the timezone in the application layer
• Adjusting the timezone in the database layer
• Conclusion

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

What timezone is MS SQL Server?

The simple answer is you have to calculate it. This can be done quite easily by using the following sql. This will actually give you offset from UTC (GMT), and not the user friendly name of the timezone. select DATEDIFF(hh, GetUtcDate(), GetDate())


http://justgeeks.blogspot.com/2008/08/what-timezone-is-ms-sql-server.html

How to set time to a date object in java

Calendar cal = Calendar.getInstance();
cal.set(Calendar.HOUR_OF_DAY,17);
cal.set(Calendar.MINUTE,30);
cal.set(Calendar.SECOND,0);
cal.set(Calendar.MILLISECOND,0);
Date d = cal.getTime();

Also See

• Joda time
• Calendar doc

http://stackoverflow.com/questions/5165428/how-to-set-time-to-a-date-object-in-java

Oracle “Partition By” Keyword

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.
In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if your de-nomalising the emp table; you still return every record in the emp table.)

emp_no  dept_no  DEPT_COUNT1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

If there was another column (e.g., state) then you could count how many departments in that State.
It is like getting the results of a GROUP BY (SUM, AVG, etc.) without the aggregation of the result set.
It is useful when you use the LAST OVER or MIN OVER functions to get, for example, the lowest and highest salary in the department and then use that in a calulation against this records salary without a sub select. It is also much faster.
Read the linked AskTom article for further details.
Hope this helps.

http://stackoverflow.com/questions/561836/oracle-partition-by-keyword

sql 2005 - The column was specified multiple times

The problem, as mentioned, is that you are selecting PEID from two tables, the solution is to specify which PEID do you want, for example

 SELECT tb.*
    FROM (
        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
    ) AS tb;

That aside, as Chris Lively cleverly points out in a comment the outer SELECT is totally superfluous. The following is totally equivalent to the first.

        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

or even

        SELECT * 
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

but please avoid using SELECT * whenever possible. It may work while you are doing interactive queries to save typing, but in production code never use it.

http://stackoverflow.com/questions/1058606/sql-2005-the-column-was-specified-multiple-times

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

How to Turn on IDENTITY_INSERT in SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON
INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)
SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

 http://stackoverflow.com/questions/7063501/how-to-turn-on-identity-insert-in-sql-server-2008

Can foreign key constraints be temporarily disabled using T-SQL?

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).
If you are deleting all the data you may find this solution to be helpful.
Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql

circular foreign key. How do i handle them?

Circular foreign keys are not really supported in SQL Server. It is possible to do it if you really want but it's not very useful since you've have no way to insert any data - you can't insert into table A because the required references in table B don't exist and vice versa. The only way would be to create one of the tables without the FK and then add it after the second table is created. Then, to insert data, you'd need to disable one of the FKs and then re-enable it but this is a very resource intensive operation if you've lots of data since it will all need to be re-checked when the FK is re-enabled. Basically, you either have to live with incomplete declarative referential integrity or, perhaps more wisely, consider remodelling your data as @munisor suggests. WARNING: the following code smaple demonstrates how to create circular FKs but this really is very bad for your health! I'm sure that in the longer run, you won't be wanting to do this. For example, simply trying to drop either of these tables after this is run is very difficult, you can't simple DROP TABLE! CREATE TABLE [A] (     [AId] int         NOT NULL         PRIMARY KEY,     [BId] int         NULL         -- You can't create the reference to B here since it doesn't yet exist! ) CREATE TABLE [B] (     [BId] int         NOT NULL         PRIMARY KEY,     [AId] int         NOT NULL         FOREIGN KEY             REFERENCES [A] ) -- Now that B is created, add the FK to A ALTER TABLE [A]     ADD         FOREIGN KEY ( [BId] )         REFERENCES [B] ALTER TABLE [A]     ALTER COLUMN [BId]         int         NOT NULL

http://stackoverflow.com/questions/2864237/circular-foreign-key-how-do-i-handle-them

SMTP local server in windows 7? (running IIS7)

This response is a little late, but might help the next person...
No, Windows 7 does not come with an SMTP server. Supposedly Remote Server Administration tools includes an SMTP server, but according to this link, it does not work.
Some options for sending email from a Windows 7 machine are:
For development purposes, I like smtp4dev http://smtp4dev.codeplex.com/. It is open source and emulates a SMTP server. However instead of actually sending the email, it keeps in in an app on the system tray. Great for making sure you don't accidentally SPAM your users.
If you are planning to send emails out for real, I would advise using a SMTP server with a static IP address as most spam filters dislike dynamic addresses and will block the email.
I have not used it, but if you must send email from your local Windows machine and am not worried about SPAM filters, Free SMTP Server from www.softstack.com seems pretty popular.

http://stackoverflow.com/questions/8041856/smtp-local-server-in-windows-7-running-iis7

How do you refresh maven dependencies from eclipse?

You generate the special eclipse files with mvn eclipse:eclipse, but once you've done that, you should let a plugin handle the dependencies while inside eclipse.
That's how we do it at my work place, and it generally works well.

http://stackoverflow.com/questions/136308/how-do-you-refresh-maven-dependencies-from-eclipse

class file has wrong version 50.0, should be 49.0″ by Maven

If this error occurs when you’re compiling your Maven project, there’s a good chance that you’ve included a Maven Dependency that was compiled using Java 1.6 and your Maven installation is running on a Java 1.5 JRE.
The good news is that Maven will tell you which Dependency is causing that. For instance, in my case it was java-web-api as shown in the error message:

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:2.3.2:compile (default-compile) on project hTask-web: Compilation failure

[ERROR] /Users/hordine/projects/hTask/hTask-web/src/main/java/com/hordine/htask/web/control/TaskController.java:[3,-1] cannot access javax.servlet.http.HttpServletRequest

[ERROR] bad class file: /Users/hordine/.m2/repository/javax/javaee-web-api/6.0/javaee-web-api-6.0.jar(javax/servlet/http/HttpServletRequest.class)

[ERROR] class file has wrong version 50.0, should be 49.0

This makes perfect sense, since if you use features of a later java version, i.e. 1.6, to build your application, then those features won’t be understood by an earlier Java JRE, i.e. 1.5, since they didn’t know what was coming in Java 1.6 yet. Yeah right.
But anyway, there are 2 quick ways to fix this. One is to find an earlier version of your Dependency, which was compiled using Java 1.5, and the other is to install a Java 1.6 JRE and get Maven to use that one. The later can be as simple as setting the JAVA_HOME environment variable to your Java 1.6 installation.
That’s it for this post.

http://hordine.wordpress.com/2012/05/02/class-file-has-wrong-version-50-0-should-be-49-0-by-maven/

JBoss debugging in Eclipse

You mean remote debug JBoss from Eclipse ?
From Configuring Eclipse for Remote Debugging:

Set the JAVA_OPTS variable as follows:

set JAVA_OPTS= -Xdebug -Xnoagent 
   -Xrunjdwp:transport=dt_socket,address=8787,server=y,suspend=n %JAVA_OPTS%

or:

JAVA_OPTS=”-Xdebug -Xnoagent 
  -Xrunjdwp:transport=dt_socket,address=8787,server=y,suspend=n $JAVA_OPTS”

In the Debug frame, select the Remote Java Application node.
In the Connection Properties, specify localhost as the Host and specify the Port as the port that was specified in the run batch script of the JBoss server, 8787.

http://stackoverflow.com/questions/516196/jboss-debugging-in-eclipse

Multiple testsuites in Cactus

http://mail-archives.apache.org/mod_mbox/jakarta-cactus-user/200304.mbox/%3COFA7163D45.FAD487C5-ON85256D11.00528FAD@ny.jpmorgan.com%3E

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

SQL Server: how to search for a table name or a column name or a given stored procedure name

This article demonstrates how the Microsoft SQL Server database can be queried to perform the following tasks:
1. fetch a list of tables.
2. fetch a list of Columns.
3. fetch a list of Stored Procedures
The results can refined further by including other search criteria in the where clause of each query.

--Query to fetch the list of table that match a pattern
select * from information_schema.tables where table_name like '%Employee%'

--Query to fetch the list of Columns (with table info) that match a pattern.
select * from information_schema.columns where column_name like '%EmpId%'

--Query to fetch the list of Stored Procedures that match a pattern.
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and routine_name like '%MyProc%' 

http://cavemansblog.wordpress.com/2009/02/26/sql-how-to-2/