Oracle GoldenGate: Working With Dates

Introduction

By default Oracle GoldenGate handles data for date data types without any user intervention. However, there are times when you may need to manipulate this data based upon end use requirements. Oracle GoldenGate has numerous built-in functions that allows users to test and manipulate data. In this article we shall present the column-conversion functions used to work with dates and timestamps: @DATE, @DATEDIFF, and @DATENOW.

Main Article

Oracle GoldenGate provides three column-conversion functions for working with date and time date:

@DATE returns a date and time based on the format passed into the source column.

@DATEDIFF returns the difference between two dates or timestamps.

@DATENOW returns the current date and time.

To demonstrate the use of each function, we’ll use a simple Oracle table as our source; defined as:

create table tpc.dt (
  col_rownum     number(38),
  col_unix_time  number(38),
  col_date       date,
  col_timestamp  timestamp(6),
  primary key (col_rownum)
);

The Oracle GoldenGate Integrated Extract configuration for this test is:

extract epdborcl
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
exttrail ./dirdat/ea
logallsupcols
updaterecordformat compact
reportcount every 5 minutes, rate
table pdborcl.tpc.*;

@DATENOW and @DATEDIFF

The two simplest functions are @DATENOW and @DATEDIFF.

The @DATENOW function returns the current date and time in the format YYYY-MM-DD HH:MI:SS. The date and time are returned in local time, including adjustments for Daylight Saving Time. @DATENOW takes no arguments, and the usage syntax is @DATENOW ().

The @DATEDIFF function calculates the difference between two dates or timestamps. The value returned may be in days or seconds. The usage syntax is @DATEDIFF (‘DD | SS’, ‘date’, ‘date’); where ‘date’ is a date or timestamp enclosed in single quotes, or the @DATENOW function specification.

To demonstrate the use of these functions, we’ll use an Oracle table as our target; defined as:

create table tpcc.dt1 (
  col_rownum    number(38),
  col_date      date,
  col_timestamp timestamp(6),
  gg_datenow    timestamp(6),
  gg_datediff_ss   number(15),
  gg_datediff_dd   number(15),
  primary key (col_rownum)
);

The Oracle GoldenGate Integrated Replicat configuration for this test is:

replicat rtpcc
useridalias ggalias
sourcecatalog pdborcl
map tpc.dt, target tpcc.dt1,
colmap (usedefaults,
gg_datenow = @datenow (),
gg_datediff_ss = @datediff (‘SS’, ‘1933-01-05’, @DATENOW ()),
gg_datediff_dd =  @datediff (‘DD’, ‘1933-01-05’, @DATENOW ())
);

Insert a source row:

insert into tpc.dt values (1, 1499794843, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
commit;

View the target results:

SQL> select * from tpcc.dt1;

COL_ROWNUM  COL_DATE
1           11-JUL-17
COL_TIMESTAMP
11-JUL-17 01.41.19.212575 PM
GG_DATENOW
11-JUL-17 01.41.35.000000 PM
GG_DATEDIFF_SS GG_DATEDIFF_DD  
266704495		30868

In the above example, we see COL_ROWNUM, COL_DATE, and COL_TIMESTAMP are populated with the values from our source table. GG_DATENOW is populated with the date and time of the target server’s operating system when Integrated Replicat processed the record from the Oracle GoldenGate Trail. The two GG_DATEDIFF columns record the difference between the birth of the GoldenGate Bridge and the current server date and time, in seconds and days.

It should be noted that this calculation does not include the end day (the calculation end day used by the function is 10-JUL-17). To include the end day in the calculation, add “1” to the colmap statement, like this: gg_datediff_dd =  @datediff (‘DD’, ‘1933-01-05’, @DATENOW () + 1).

It should also be noted that when seconds are specified for @DATEDIFF computations are based upon timestamps; so, the start date used is actually 1933-01-05 00:00:00 and the end date 10-JUL-17 01:41:43 PM.

@DATE

The @DATE function returns dates and times in a variety of formats to the target column based upon the format passed into the source column. @DATE converts virtually any type of input into a valid SQL date, may be used to extract portions of a date column, or compute a numeric timestamp column based upon a date. The usage syntax is @DATE (‘output format’, ‘input format’, ‘source column’).

For more details on the input and output format descriptors, refer to the Oracle Fusion Middleware Reference for Oracle GoldenGate for Windows and UNIX documentation on the Oracle Tech Network website.

To demonstrate the use of this function, we’ll use an Oracle table as our target; defined as:

create table tpcc.dt2 (
  col_rownum char(38),
  col_date      date,
  col_timestamp timestamp(6),
  gg_ts_unix_time timestamp(6),
  gg_century char(2),
  gg_yy char(2),
  gg_yyyy char(4),
  gg_mm char(2),
  gg_mmm char(3),
  gg_dd char(2),
  gg_ddd char(3),
  gg_dow0 char(1),
  gg_dow1 char(1),
  gg_dowa char(3),
  gg_hh char(2),
  gg_mi char(2),
  gg_ss char(2),
  gg_ff char(6),
  gg_jtslct varchar(30),
  gg_jtsgmt varchar(30),
  gg_jts char(11),
  gg_jul char(5),
  primary key (col_rownum)
);

The Oracle GoldenGate Integrated Replicat configuration for this test is:

replicat rtpcc
useridalias ggalias
sourcecatalog pdborcl
map tpc.dt, target tpcc.dt2,
colmap (usedefaults,
gg_ts_unix_time = @date (‘YYYY-MM-DD HH:MI:SS.FFFFFF’, ‘CDATE’, COL_UNIX_TIME),
gg_century = @date (‘CC’, ‘YYYY-MM-DD’, COL_DATE),
gg_yy = @date (‘YY’, ‘YYYY-MM-DD’, COL_DATE),
gg_yyyy = @date (‘YYYY’, ‘YYYY-MM-DD’, COL_DATE),
gg_mm = @date (‘MM’, ‘YYYY-MM-DD’, COL_DATE),
gg_mmm = @date (‘MMM’, ‘YYYY-MM-DD’, COL_DATE),
gg_dd = @date (‘DD’, ‘YYYY-MM-DD’, COL_DATE),
gg_ddd = @date (‘DDD’, ‘YYYY-MM-DD’, COL_DATE),
gg_dow0 = @date (‘DOW0’, ‘YYYY-MM-DD’, COL_DATE),
gg_dow1 = @date (‘DOW1’, ‘YYYY-MM-DD’, COL_DATE),
gg_dowa = @date (‘DOWA’, ‘YYYY-MM-DD’, COL_DATE),
gg_hh = @date (‘HH’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_mi = @date (‘MI’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_ss = @date (‘SS’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_ff = @date (‘FFFFFF’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_jtslct = @date (‘JTSLCT’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_jtsgmt = @date (‘JTSGMT’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_jts = @date (‘JTS, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP),
gg_jul = @date (‘JUL’, ‘YYYY-MM-DD HH:MI:SS.FFFFFF’, COL_TIMESTAMP)
);

Generate source data:

insert into tpc.dt values (2, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
commit;

insert into tpc.dt values (3, 1499871217, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
update tpc.dt set COL_UNIX_TIME = 1499871217, COL_TIMESTAMP = CURRENT_TIMESTAMP 
 where col_rownum = 2;
commit;

View the target results:

SQL> select * from dt2 where col_rownum = 2;

COL_ROWNUM	COL_DATE    COL_TIMESTAMP
2		12-JUL-17   12-JUL-17 10.54.49.573221 AM

GG_TS_UNIX_TIME 							     
12-JUL-17 10.53.37.000000 AM

GG_YY GG_YYYY GG_MM GG_MMM 
   17    2017    07    JUL

GG_DD GG_DDD GG_DOW0 GG_DOW1 GG_DOWA   
   12    193       0       0     WED     

GG_HH GG_MI GG_SS GG_FF
   10    54    49 573221

GG_JTSLCT           GG_JTSGMT
212366616889573221  212366631289573221

GG_JTS	     GG_JUL
21236663128  24579

SQL> select * from dt2 where col_rownum = 3;

COL_ROWNUM	COL_DATE    COL_TIMESTAMP
3		12-JUL-17   12-JUL-17 10.54.49.569929 AM

GG_TS_UNIX_TIME 							     
12-JUL-17 10.53.37.000000 AM

GG_YY GG_YYYY GG_MM GG_MMM 
   17    2017    07    JUL

GG_DD GG_DDD GG_DOW0 GG_DOW1 GG_DOWA   
   12    193       0       0    WED    

GG_HH GG_MI GG_SS GG_FF
   10    54    49 569929

GG_JTSLCT            GG_JTSGMT
212366616889569929   212366631289569929

GG_JTS	     GG_JUL
21236663128   24579

A Real-World Example

From the examples above you see the basic functionality of the Oracle GoldenGate date functions; but, how does this correlate to real-world usage? I recently worked with a customer who had an interesting requirement. In their target table they defined three columns that did not exist in the source:

CREATE_DATE    NUMBER(14)
UPDATE_DATE    NUMBER(14)
UPDATE_TS      TIMESTAMP(0)

The replication requirement was: for every source record, if the operation is an insert populate the CREATE_DATE column with the current timestamp with a format of YYYYMMDDHHMISS, for all operations do the same for the UPDATE_DATE column, and for all operations populate UPDATE_TS with the Julian Timestamp of the target server’s operating system.

This would have been very easy to do, except for the definition of the target columns; NUMBER instead of VARCHAR for CREATE_DATE and UPDATE_DATE; and then the requirement to generate a timestamp based upon the operating system’s time presented as a Julian Timestamp. This meant I had to (1) get the current timestamp from the operating system, strip out the “-” and “:” characters, and convert the character data to numeric data, and (2) get the Julian Timestamp from the operating system and convert it to an Oracle timestamp.

To demonstrate the solution, we’ll use the following target table and the source test table previously defined:

create table tpcc.dt (
  col_rownum     number(38),
  col_unix_time  number(38),
  col_date       date,
  col_timestamp  timestamp(6),
  create_date    number(14),
  update_date    number(14),
  update_ts      timestamp(0),
  primary key (col_rownum)
);

With Integrated Replicat configured as:

replicat rtpcc
useridalias ggalias
sourcecatalog pdborcl
map tpc.dt, target tpcc.dt,
colmap (usedefaults,

— If the operation is an insert, take the current date and convert it to a number
— with a format of yyyymmddhhmiss
create_date = @CASE (
@GETENV (‘GGHEADER’, ‘OPTYPE’), ‘INSERT’, @NUMSTR (
@STRCAT (
@STREXT (@DATENOW (),1,4),
@STREXT (@DATENOW (),6,7),
@STREXT (@DATENOW (),9,10),
@STREXT (@DATENOW (),12,13),
@STREXT (@DATENOW (),15,16),
@STREXT (@DATENOW (),18,19)
)),
@COLSTAT (MISSING)
),

— For all operations, take the current date and convert it to a number
— with a format of yyyymmddhhmiss
update_date = @NUMSTR ( @STRCAT (
@STREXT (@DATENOW (),1,4),
@STREXT (@DATENOW (),6,7),
@STREXT (@DATENOW (),9,10),
@STREXT (@DATENOW (),12,13),
@STREXT (@DATENOW (),15,16),
@STREXT (@DATENOW (),18,19)
)
),
update_ts = @date (‘yyyy-mm-dd hh:mi:ss’, ‘JTS’, @getenv (‘JULIANTIMESTAMP’) )
);

Generate source data:

insert into tpc.dt values (4, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
commit;

update tpc.dt set COL_UNIX_TIME = 1499871217, COL_TIMESTAMP = CURRENT_TIMESTAMP 
  where col_rownum = 4;
commit;

Verify the target data:

ggdt1

As you may see above, the additional columns are populated with date and timestamp data in the required format. But what if the CREATE_DATE and UPDATE_DATE columns were character data types and the target table that looks like this:

create table tpcc.dt (
  col_rownum     number(38),
  col_unix_time  number(38),
  col_date       date,
  col_timestamp  timestamp(6),
  create_date    varchar(14),
  update_date    varchar(14),
  update_ts      timestamp(0),
  primary key (col_rownum)
);

We can now simplify our Integrated Replicat configuration:

replicat rtpcc
useridalias ggalias
sourcecatalog pdborcl
map tpc.dt, target tpcc.dt,
colmap (usedefaults,

— If the operation is an insert, take the current date and convert it to a number
— with a format of yyyymmddhhmiss
create_date = @CASE (
@GETENV (‘GGHEADER’, ‘OPTYPE’), ‘INSERT’,
@DATE (‘YYYYMMDDHHMISS’, ‘YYYY-MM-DD HH:MI:SS’, @DATENOW ()),
@COLSTAT (MISSING)
),

— For all operations, take the current date and convert it to a number
— with a format of yyyymmddhhmiss
update_date = @DATE (‘YYYYMMDDHHMISS’, ‘YYYY-MM-DD HH:MI:SS’, @DATENOW ()),

— For updates, get the commit timestamp of the source record; otherwise,
— set to null.
update_ts = @DATENOW ()
);

Insert data into the source table:

insert into tpc.dt values (5, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
insert into tpc.dt values (6, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
commit;

Validate the target data:

ggdt2aWe have the same results, with less possibility for syntax errors.

Summary

In this article we reviewed the Oracle GoldenGate Column Conversion functions for working with date data types.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

Comments

  1. Boris Tyukin says:

    actually, I think I found an answer. Great post from Jinyu:
    https://jinyuwang.weebly.com/core-platform/oracle-goldengate-managing-the-timezone-difference

  2. Boris Tyukin says:

    Hi Loren, I am curious if it is possible to convert _all_ date/time columns from one time zone to another using replicat along. In other words, if source system has timestamps in UTC time and I need to convert to Eastern, but also without hardcoding listing all the column names in the parameters file. I was trying to find some global settings in the docs. Logdump tool does recognize the time zone offset in the source DB and I was hoping I can then tell replicat to “convert all dates from extract to Eastern time zone”.

Add Your Comment