Date of birth conversion error [from Epoch timestamp] during data ingestion

[sampathboyapati]

Hi, we are facing an issue while converting the date field coming from the source in the format of io.debezium.time.Date. It actually represents the date of birth in number of days [Number of days from the beginning of Unix epoch i.e. 1 January, 1970]. For example, if date of birth value is 1, then the date should be 2 January, 1970.

We are trying to convert the incoming data from io.debezium.time.Date format to actual readable date format such as 1980-11-18. We are ingesting the data in batch mode. Function we are using to modify the input field is dformat(dt_of_brth*86400000, "yyyy-MM-dd'T'HH:mm:ss.SSSX") 

Here we are trying to convert the dt_of_birth in days format to epoch timestamp format by multiplying with 86400000 Milliseconds, which can then be converted to readable date format using dformat function. But the result is not as expected and giving null for the dt_of_birth values from 1 to 24. For dt_of_birth values from 25, above function is working fine and giving expected results. Can anyone please help us with the information what's going wrong here?

Thanks,

Sampath

[_Manoj_Kumar_ · Accepted solution]

Hello @sampathboyapati ,

Here is something you can try to make it work.

iif(dt_of_brth > 24,dformat(dt_of_brth*86400000, "yyyy-MM-dd"),format(make_date_time(1970, 01, dt_of_brth, 0, 0, 0, 0, "America/Los_Angeles"),"yyyy-MM-dd"));

Let me know if that works for you.