Solved

timestamp tz to ntz utc conversion.

  • 21 July 2023
  • 6 replies
  • 120 views
A
Rank

Hi, I have a record value in the format 2023-07-20T22:50:10Z utc time zone. 
case 1: I need to convert that to NTZ and add 8 hours to it to compare with the last loaded NTZ timestamp that is UTC+8 hours. Please 

case2: I need to preserve the same by only removing TZ format. 

Please could someone provide me help. Thanks. 

icon

Best answer by Bikram 23 July 2023, 07:50

View original

6 replies

Bikram
Rank
Userlevel 5
Badge +1

@anand_rajan 

can you please provide the details in clear ,so i can help you on it.

Input Paramter With Example

Expected output for your case 1 and case 2 .

A
Rank

Hi @Bikram 

Thanks for your reply. I am trying to read the SharePoint csv files and then compare the datetime with the last load datetime for delta load or a default datetime for initial load. After getting the api response from the http client I pass the data to an expression evaluator where I extract the file name and datetime part as string. Then I use check conditions to check the extracted date time greater than either initial load or delta load datetime. 

 

 As of now I have two options to extract date time from two different places. Please find below my case and formats of date time. 

 

Case 1:

Extract date time from file name. 

 

Filename_datetime. E.g filename1_2022-07-01 16:08.csv

 

After extracting the above as string, I tried to do a field type converter but I’m unable to do a proper comparison or use the right conversion function and compare with initial or delta load date. P.S.  the browser timezone is utc. 

 

Case2:

I have another option to extract the last modified time from api and then compare it with the initial or delta loaded dates. The format of that is 2023-07-20T22:50:10Z. In this case, I need to convert this time to utc plus 8 hours and then compare it with my date time that is stored without TZ format and utc+8 hours. So after conversion the above timestamp the expected output would be 2023-07-21 06:50:10:000. Then I need to use a function to compare that to my table date time that is yyyy-mm-d hh:MM:ss:SSS format. 

 

So, I would like to understand how to convert these two cases to proper required timestamp that has No Timezone and that is utc plus 8 hour format. And then use a function to be used to compare it with the another date time yyyy-MM-d hh:mm:ss:SSS format. 
 

Kind Regards

Bikram
Rank
Userlevel 5
Badge +1

@anand_rajan 

 

Kindly try the below code snippet for time conversion using jython and check if it helps in your case.

You can use time conversion to convert the time to the required format , please let me know if you are having issues/concerns , will try  to help you on it .

 

from java.sql import Timestamp
from java.util import Calendar
from java.sql import Timestamp
from java.text import SimpleDateFormat
from java.util import Date
#from dateutil.relativedelta import relativedelta

# Sample Jython code


date_string = "2023-05-09T07:08:58.847Z"

for record in sdc.records:
  try:

    date_string = record.value['f1']
    date_format = SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
    date_obj = date_format.parse(date_string)

    timestamp = Timestamp(date_obj.getTime())
    result_timestamp = Timestamp(timestamp.getTime() + 6 * 60 * 60 * 1000)

    record.value = result_timestamp
    sdc.output.write(record)

  except Exception as e:
    # Send record to error
    sdc.error.write(record, str(e))

 

 

 

Bikram
Rank
Userlevel 5
Badge +1

@anand_rajan 

 

For date time string comparison , please refer the below code snippet and check if it helps.

 

Attaching the pipeline for your reference.

 

 

 

1 Attachment
A
Rank

Hi @Bikram Thank you so much for the details and help. Highly appreciated. For the case where the field type converter used, I would like to mention the scenario again. I have incoming string data in which I will extract the timestamp value using regex eg. output of regex would be like 2023-02-01 16:04.
 

Then I shall use field type converter to convert that string to required timestamp format. E.g. 2023-02-01 16:04:00:000. After that, I need to check condition like “ 2023-02-01 16:04:00:000” > “delta or hardcoded timestamp”?
 

Is it like using function ${time:extractDateFromString(record:value)? 
 

 

————
Finally, if you have time and could share some details for the below question please provide your help  thanks. 

 

kind regards. 

Bikram
Rank
Userlevel 5
Badge +1

@anand_rajan 

The datetime values consistently follow the format provided below. To perform comparisons, maintain both the date field value and the default date value as strings. By comparing the strings, if the result is 0, it indicates no changes; otherwise, changes exist in the data for other cases.

 

e.g 

Test1 = “2023-02-01 16:04:00”

Default date values as in Test2 =  “2023-02-01 16:04:01”

 

str.compare(Test1 ,Test2)

if string matches , returns true else false for other-cases. Below the screen shot for your reference.

 

 

Reply


Terms & Conditions