Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 1

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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.

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

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.