[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4178
  • Last Modified:

UTC Time conversion within sybase


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.
1 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now