UTC Time conversion within sybase

Posted on 2009-04-20
Last Modified: 2012-08-14

I am trying to convert a normal time that is stored in sybase to UTC time and back.

Reason: In my compnay, there are 2 tables, one table stores the time in UTC and I have to compare that to another time in another table which is NOT stored in UTC time, hence, there was time differences between the two table.

My current solution was to take one time, subtract 4 compare to the other table and then add back 4. But apparently I cant do that because of daylitght savings.

So ultimately my question is, is there a way to manipulate UTC time and normal time in sybase, thanks.
Question by:Kev84
    1 Comment
    LVL 19

    Accepted Solution

    Since you did not give us much information, I will have to assume one of the 4 or 5 Sybase database families and pick ASE.  Not knowing the version or what options you have is kind of a handicap.

    The normal way to handle these things is that ALL data is stored in the database in UTC/GMT which never has DST.  All conversions to/from local time should be carried out by the application code.  Breaking this rule leads to all kinds of bad juju.
     First, you don't have to "...add back 4" since the modification to the date takes place in the WHERE cluase.

    Next, you can do simple date math, e.g.   WHERE A.date_field = dateadd(HR,@UTC_OFFSET,B.date_field)
    in the WHERE clause to handle the comparison.  It is not pretty but it works.

    Your other options depend on version and options installed.  If you have 12.5.x with the Java Option licensed or have 15.x with the Java option turned on, you can create a simple inline (SQLJ) function that uses the Java SimpleTimeZone class to handle this stuff for you.  Likewise, if you have 15.0.2 or higher, you can create a T-SQL User Defined Function (UDF) to clean things up.  You still have to handle figuring out what the delta is between the local timezone and UTC though.

    Unless you are using Java, you are going to have to store the delta between UTC and the local timezone someplace in the database.  You will have to have a process that changes it twice a year as DST comes and goes.  Of course, this exposes the real problem with your system design which is that you are storing local date/time in the database at all.  For instance, on those nights when we get the extra hour of sleep, you will have two different sets of transactions overlapping for the same one hour period.  All kinds of nasty things can happen from there depending upon how important the date/time is to your application.  If you are using the loacl date/time in a primary key or unique index, you are probably screwed.

    You can create a little one-column, one-row table that holds the offset and us it in your T-SQL function.  A faster method is to create a view that contains the value, e.g.


    This turns out to be very much faster than a table access since the query optimizer is smart enough to treat this as a constant early on in the process.  This has the advantage of allowing you to join the UTC_OFFSET table into any query that needs the OFFSET value so you don't have to create functions; handy if you are running 12.5.x without the Java Option.

    It sounds as if this is a new problem which means the system is probably new as well.  If that is the case, get the application folks to do the conversion and keep everything in the database as UTC.  Anything else will drain performance and cause problems down the road.  If you make a stink now, when they occur you can say I told you so instead of being blamed for them.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now