It’s not a secret that Remedy stores dates using the epoch date format in number fields. The date is referred to the Greenwich reference time, or Zulu time. So a little adjustment must be made to translate this time to you time zone. This offset is composed by a fixed value and a variable value that depends on Daylight Time Savings (DTS).

If using Analytics or simply want to do some trick at the database level, a conversion utility between epoch a Oracle date format is very useful.

In this post I will cover this problem and I will provide you with the PL/SQL code of the conversion.

The EPOCH format

The epoch date format is a number that measures the number of second that has passed from one particular date (January, the first of 1970). So Epoch 0, is 1/1/1970 at 00:00:00. Epoch 1 is 1/1/1970 at 00:00:01. And Epoch 86400 is 2/1/1970, since 86400 is the number of seconds within a day. And Epoch -1 is 31/12 at 23:59:59.

So obtaining the direct related date is straightforward.

CREATE OR REPLACE FUNCTION FN_DATE_TO_EPOCH
(IN_DATE IN DATE)
RETURN NUMBER IS
   OUT_DATE NUMBER;
BEGIN

   OUT_DATE:= IN_DATE - TO_DATE('1/1/1970')*86400;

   RETURN OUT_DATE;

END FN_EPOCH_TO_DATE;

But don’t forget that the obtained date is Zulu time. We must adjust it the time difference of our local time zone and adjust the DST. The first part is easy to achive, since is adding or subtracting some fixed value to the result. But DST can present a hard problem.

CREATE OR REPLACE FUNCTION FN_DATE_TO_EPOCH
(IN_DATE IN DATE)
RETURN NUMBER IS
   OUT_DATE NUMBER;
BEGIN

   OUT_DATE:= IN_DATE - TO_DATE('1/1/1970')*86400 - 3600;

   RETURN OUT_DATE;

END FN_EPOCH_TO_DATE;

The fn_adjusted_date function

If you have Analytics installed, you will have the fn_adjusted_date function in your system. This function can detect your timezone, add the difference, take care of DST and covert from epoch to date.

So half the problem is solved. Let’s solve the other half.

The solution for date to Epoch conversion

The nest trick bases its functionality over the fn_adjusted_date function. So, anything that has been taken in consideration when designing the fn_adjusted_date function, will be propagated to our solution.

The idea is to make the basic conversion. Next convert the result to a date using the BMC’s function. See the drift between both results, and correct the result by this drift.

CREATE OR REPLACE FUNCTION FN_DATE_TO_EPOCH
(IN_DATE IN DATE)
RETURN NUMBER IS
   OUT_DATE NUMBER;
   V_DATE DATE;
BEGIN

   OUT_DATE:= IN_DATE - TO_DATE('1/1/1970')*86400;
   V_DATE:= fn_adjusted_date(OUT_DATE);
   OUT_DATE:= OUT_DATE + ((IN_DATE - V_DATE)*86400);

   RETURN OUT_DATE;

END FN_EPOCH_TO_DATE;

You can create a converter that directly considers the DST and time difference. But this solution will have the next advantages: