Introduction
Oracle GoldenGate 23ai is a powerful solution for real-time data replication, offering seamless handling of date and timestamp data types in heterogeneous environments. While GoldenGate manages these data types by default, specific use cases may require custom manipulation.
GoldenGate 23ai provides robust column-conversion functions — @DATE, @DATEDIFF, @DATENOW, and the new @DBFUNCTION to transform and process date and time data. This article explores these functions with practical examples, highlighting their application in Oracle GoldenGate 23ai’s Microservices Architecture.
Overview of Date Functions
Oracle GoldenGate 23ai offers four key functions for working with dates and timestamps:
- @DATE: Converts and formats date or time data based on specified input and output formats.
- @DATEDIFF: Calculates the difference between two dates or timestamps, returning results in days or seconds.
- @DATENOW: Retrieves the current date and time of the target system.
- @DBFUNCTION: Invokes database functions or stored procedures during replication, enabling dynamic data manipulation.
To demonstrate these functions, we’ll use a sample Oracle source table defined as:
CREATE TABLE source_schema.dt (
col_rownum NUMBER(38),
col_unix_time NUMBER(38),
col_date DATE,
col_timestamp TIMESTAMP(6),
PRIMARY KEY (col_rownum)
);
The Oracle GoldenGate 23ai Extract configuration (using Microservices Architecture) is:
EXTRACT epdborcl
USERIDALIAS ggalias
EXTTRAIL as
LOGALLSUPCOLS
REPORTCOUNT EVERY 5 MINUTES, RATE
TABLE dbcsash_pdb1.source_schema.*;
Note: GoldenGate 23ai no longer supports Classic Architecture; Microservices Architecture is required.
Using @DATENOW and @DATEDIFF
@DATENOW
The @DATENOW function returns the current date and time in the format YYYY-MM-DD HH:MI:SS, adjusted for local time and Daylight-Saving Time. It requires no arguments and is invoked as @DATENOW().
@DATEDIFF
The @DATEDIFF function computes the difference between two dates or timestamps, returning results in days (DD) or seconds (SS). Its syntax is:
@DATEDIFF(‘DD | SS’, ‘date1’, ‘date2’)
Here, date1 and date2 can be static dates, timestamps, or @DATENOW().
Example:
We’ll replicate data to a target table defined as:
CREATE TABLE target_schema.dt_tgt1 (
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 Replicat configuration is:
REPLICAT rtpcc
USERIDALIAS ggalias
SOURCECATALOG dbcsash_pdb1
MAP source_schema.dt, TARGET target_schema.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 source_schema.dt
VALUES (1, 1499794843, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT;
Query the target table:
SQL> select * from target_schema.dt_tgt1;
COL_ROWNUM COL_DATE COL_TIMESTAMP GG_DATENOW GG_DATEDIFF_SS GG_DATEDIFF_DD
———- ———- —————————— —————————— ————– ————–
1 07-MAY-25 07-MAY-25 02.34.46.574743 PM 07-MAY-25 03.17.15.000000 PM 2913895035 33725
Explanation:
- COL_ROWNUM, COL_DATE, and COL_TIMESTAMP is directly replicated from the source table.
- GG_DATENOW captures the target server’s date and time when the Replicat processes the record from the Oracle GoldenGate trail.
- GG_DATEDIFF_SS and GG_DATEDIFF_DD calculate the difference between January 5, 1933 (the start of Golden Gate Bridge construction) and the current server date and time, in seconds and days, respectively.
- The @DATEDIFF calculation for days (DD) excludes the end day (e.g., it uses May 6, 2025, as the end date). To include the end day, modify the COLMAP statement in the Replicat configuration as follows: gg_datediff_dd = @datediff (‘DD’, ‘1933-01-05’, @DATENOW () + 1)
- When using seconds (SS) in @DATEDIFF, the computation is based on timestamps.
For example:
Start date: 1933-01-05 00:00:00
End date: 2025-05-07 03:17:15 PM
Using @DATE
The @DATE function transforms date or time data into various formats, converts inputs to valid SQL dates, extracts date components, or computes numeric timestamps. Its syntax is:
@DATE(‘output_format’, ‘input_format’, source_column)
For format descriptors, refer to the Oracle GoldenGate 23ai Documentation.
Example
We’ll use a target table with multiple date-derived columns:
CREATE TABLE target_schema.dt_tgt2 (
col_rownum CHAR(38),
col_date DATE,
col_timestamp TIMESTAMP(6),
gg_ts_unix_time TIMESTAMP(6),
gg_century CHAR(2),
gg_yyyy CHAR(4),
gg_mm CHAR(2),
gg_mmm CHAR(3),
gg_dd CHAR(2),
gg_ddd CHAR(3),
gg_dowa CHAR(3),
gg_hh CHAR(2),
gg_mi CHAR(2),
gg_ss CHAR(2),
gg_ff CHAR(6),
gg_jts CHAR(11),
gg_jul CHAR(5),
PRIMARY KEY (col_rownum)
);
The Replicat configuration is:
REPLICAT rtpcc
USERIDALIAS ggalias
SOURCECATALOG dbcsash_pdb1
MAP source_schema.dt, TARGET target_schema.dt_tgt2,
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_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_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_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)
);
Insert source data:
INSERT INTO source_schema.dt
VALUES (2, 1499871217, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT;
Query the target table for col_rownum = 2:
SELECT * FROM target_schema.dt_tgt2 WHERE col_rownum = ‘2’;
Explanation:
- GG_TS_UNIX_TIME converts col_unix_time to a timestamp.
- Columns like GG_YYYY, GG_MMM, and GG_DOWA extract specific date components.
- GG_JTS and GG_JUL provide Julian timestamp representations.
Using @DBFUNCTION
Introduced in GoldenGate 23ai, @DBFUNCTION allows invoking database functions or stored procedures during replication, enabling dynamic data processing. This is helpful for applications that are tracking the database timestamp, time-sensitive operations, or ETL loads where the Apply Time of the DML operation within the database is needed.
For more information, refer to the Oracle GoldenGate 23ai Documentation.
Example
We’ll use @DBFUNCTION to apply this formatting in the target table:
CREATE TABLE target_schema.dt_tgt3 (
col_rownum NUMBER(38),
col_date DATE,
col_timestamp TIMESTAMP(6),
col_datenow TIMESTAMP(6),
col_systemtimestamp TIMESTAMP(6),
col_systemtimestamp_char NUMBER(38),
PRIMARY KEY (col_rownum)
);
The Replicat configuration is:
REPLICAT rtpcc
USERIDALIAS ggalias
SOURCECATALOG dbcsash_pdb1
MAP source_schema.dt, TARGET target_schema.dt_tgt3,
COLMAP (
USEDEFAULTS,
COL_DATENOW = @DATENOW(),
COL_SYSTEMTIMESTAMP = @DBFUNCTION(‘SYSTIMESTAMP’),
COL_SYSTEMTIMESTAMP_CHAR = @DBFUNCTION(‘TO_CHAR(SYSTIMESTAMP,”YYYYMMDDHHMISSFFFFFF”)’)
);
Insert a source row:
INSERT INTO source_schema.dt VALUES (3, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT;
Query the target table:
SELECT * FROM target_schema.dt_tgt3;
Output:
| COL_ROWNUM |
COL_DATE |
COL_TIMESTAMP |
COL_DATENOW |
COL_SYSTEMTIMESTAMP |
COL_SYSTEMTIMESTAMP_CHAR |
| 3 |
07-May-25 |
07-MAY-25 05.11.46.729429 PM |
08-MAY-25 06.18.56.000000 AM |
08-MAY-25 06.18.56.477147 AM |
20250508061856477147477147477147 |
Explanation:
- With @DBFUNCTION(‘SYSTIMESTAMP’) you can get precise timestamp for the system
- Also use Database function TO_CHAR to convert system time to number format
Real-World Example
Consider a customer requiring three additional columns in the target table, leveraging 23ai’s features:
CREATE TABLE target_schema.dt_tgt (
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(6),
PRIMARY KEY (col_rownum)
);
Requirements:
-
For inserts, populate create_date with the current timestamp in YYYYMMDDHHMISS format (as a number).
- For all operations, populate update_date with the current timestamp in YYYYMMDDHHMISS format (as a number)..
- For all operations, populate update_ts with the Julian timestamp of the target server.
Solution:
The Replicat configuration is:
replicat rtpcc
useridalias ggalias
sourcecatalog dbcsash_pdb1
MAP source_schema.dt, TARGET target_schema.dt_tgt,
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.FFFFFFFFF’, ‘JTS’, @getenv(‘JULIANTIMESTAMP’) )
);
Insert and update source data:
INSERT INTO source_schema.dt VALUES (4, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT;
UPDATE source_schema.dt SET col_unix_time = 1499871217, col_timestamp = CURRENT_TIMESTAMP WHERE col_rownum = 4;
COMMIT;
Output (example):
| COL_ROWNUM |
COL_UNIX_TIME |
COL_DATE |
COL_TIMESTAMP |
CREATE_DATE |
UPDATE_DATE |
UPDATE_TS |
| 4 |
1499871164 |
08-May-25 |
08-MAY-25 10.53.09.786765 AM |
20250508105316 |
20250508105316 |
08-MAY-25 10.53.16.921234921 AM |
| COL_ROWNUM |
COL_UNIX_TIME |
COL_DATE |
COL_TIMESTAMP |
CREATE_DATE |
UPDATE_DATE |
UPDATE_TS |
| 4 |
1499871217 |
08-May-25 |
08-MAY-25 10.54.33.523181 AM |
20250508105316 |
20250508105439 |
08-MAY-25 10.54.39.967529967 AM |
Alternative: Use @DBFUNCTION
Replicat configuration:
REPLICAT rtpcc
USERIDALIAS ggalias
sourcecatalog dbcsash_pdb1
MAP source_schema.dt, TARGET target_schema.dt_tgt,
COLMAP (
USEDEFAULTS,
create_date = @CASE(
@GETENV(‘GGHEADER’, ‘OPTYPE’), ‘INSERT’,
@DBFUNCTION (‘TO_CHAR(SYSTIMESTAMP, ”YYYYMMDDHHMISS”)’),
@COLSTAT(MISSING)
),
update_date = @DBFUNCTION (‘TO_CHAR(SYSTIMESTAMP, ”YYYYMMDDHHMISS”)’),
update_ts = @DBFUNCTION(‘TO_CHAR(SYSTIMESTAMP,”YYYYMMDDHHMISSFFFFFF”)’)
);
Insert source data:
INSERT INTO source_schema.dt VALUES (6, 1499871164, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT;
UPDATE source_schema.dt SET col_unix_time = 1499871217, col_timestamp = CURRENT_TIMESTAMP WHERE col_rownum = 6;
COMMIT;
This approach minimizes complexity using @DBFUNCTION.
Output (example):
| COL_ROWNUM |
COL_UNIX_TIME |
COL_DATE |
COL_TIMESTAMP |
CREATE_DATE |
UPDATE_DATE |
UPDATE_TS |
| 6 |
1499871164 |
08-May-25 |
08-MAY-25 12.09.20.197742 PM |
20250508120925 |
20250508120925 |
08-MAY-25 12.09.25.346415346 PM |
| COL_ROWNUM |
COL_UNIX_TIME |
COL_DATE |
COL_TIMESTAMP |
CREATE_DATE |
UPDATE_DATE |
UPDATE_TS |
| 6 |
1499871217 |
08-May-25 |
08-MAY-25 12.09.37.188526 PM |
20250508120925 |
20250508120942 |
08-MAY-25 12.09.42.365727365 PM |
Conclusion
Oracle GoldenGate 23ai’s @DATE, @DATEDIFF, @DATENOW, and @DBFUNCTION functions provide flexible tools for date and timestamp manipulation.
For more resources, visit the Oracle GoldenGate 23ai Documentation.