How to Sync Current Date from Greenplum to Oracle with Only the Date Part?

Shahbaz
Shahbaz Registered Posts: 8 ✭✭

I have added a current_date column to my table in Greenplum using a Prepare recipe (with now() in Formula language). I want to sync this column to an Oracle database, but I need to keep only the date part of the value. For example, I want to convert a value like 2025-01-25T21:50:28.102Z into 2025-01-25 and store it as a DATE data type in Oracle. How can I achieve this?

Additionally, when I sync a value like 2025-01-25T21:50:28.102Z from Greenplum to Oracle, there is a 4-hour difference in the time. What could be causing this? For instance, does the now() formula in Greenplum consider a different time zone than my local system time?

Thank you in advance for your help!

Operating system used: windows

Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,248 Neuron

    Dataiku is backend is Java based. For that reason it does not have native support for timeless dates. In v12.6.4 Dataiku added this work around but it's a bit of a kludge:

    "SQL: Added ability to write DSS dates (i.e. date+timestamp) into existing SQL tables with a SQL date (i.e. date only) type"

    You have to go to the dataset Settings ⇒ Advanced and change "Table creation mode" to manual. Then change the column type from timestamp to date as appropiate to your databse technology. Re-run the recipe and now the date should have no time portion in your database. However you can not see in Dataiku, as Dataiku will still load the data into a timestamp Java object so you will need to use an external query tool to confirm it works. Finally setting the dataset "Table creation mode" to manual means you will have to manage dataset schema changes yourself and you will not be able to propagate schema changes in the flow. For those reasons it might be better to create the date as a string in Dataiku (YYYY-MM-DD) and persist it as that (ie VARCHAR2 in Oracle). This should prevent timezone issues as well which is why you got time differences. Dataiku will assume the data is on UTC time zone unless you tell it otherwise.

Setup Info
    Tags
      Help me…