AnsweredAssumed Answered

SQL Date conversion from UNIX for Active Directory

Question asked by Marta Benedetti on Dec 18, 2020
Latest reply on Dec 30, 2020 by Marta Benedetti

Like • Show 1 Like1
Comment • 8

Hi,

I have a request form with a field "accountExpire" of type "Date Field".

Is there a way to convert the date received from the request form in a FILEDATE format con attribute "accountExpires" in AD using a sql Select node?

I've tried this query

select
    to_number((( TO_DATE( ${PublicData_Onboarding_accountExpire}, 'MM/DD/YYYY HH:mi PM') - TO_DATE('1970-01-01', 'YYYY-MM-DD') )) * 86400 + 11644473600 + 18000) * 10000000 as accountExpire
FROM
    dual

The problem though is the the value received in the workflow from the request form is a UNIX TIMESTAMP, so this conversion is not working.

Do you have other suggestions?

Thanks,

Marta

Ahmed Nofal

Dec 29, 2020 12:36 PM

Correct Answer

Here are the queries with a 1 day or 24H addition to the originally picked date:

Time zone generically fetched:

select to_char(((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone (select SESSIONTIMEZONE from dual)) + interval '1' day,'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual

Rome/Europe hardcoded time zone:

select to_char((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone 'Europe/Rome' + interval '1' day,'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual

See the reply in context

1 person had this question

Outcomes

8 Replies

Ahmed Nofal Dec 20, 2020 6:08 PM
Mark Correct
Correct Answer
try this:
```
Select to_char(cast(to_date('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')+<Epoch Date to convert>/86400 as timestamp with local time zone),'DD/MM/YYYY HH24:MI:SS') from dual; 
```
Reference:
Convert UNIX Epoch Date to Oracle Display Value
Like • Show 0 Likes0
Actions
- Marta Benedetti @ Ahmed Nofal on Dec 21, 2020 3:53 AM
  Mark Correct
  Correct Answer
  Hi Ahmed,
  thanks for your help but it's still not working: the SQL Select node fails.
  Furthermore, it seems that this query doesn't convert from UNIX to FILEDATE format, am I right?
  
  Marta
  Like • Show 0 Likes0
  Actions
  - Ahmed Nofal @ Marta Benedetti on Dec 21, 2020 10:37 AM
    Mark Correct
    Correct Answer
    Try and test this to verify the results are as expected:
    select TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * ${PublicData_Onboarding_accountExpire} as TIMESTAMP), (select dbtimezone from dual)), 'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual
    
    Then feed the resulted WF variable value to the AD connector in the "Update an Account" capability under the "accountExpires" parameter as a value (make sure that the accountExpires parameter is of type STRING).
    1 person found this helpful
    Like • Show 1 Like1
    Actions
    - Marta Benedetti @ Ahmed Nofal on Dec 29, 2020 5:23 AM
      Mark Correct
      Correct Answer
      Hi Ahmed,
      
      thanks a lot for your help.
      Unfortunately there is still something that is not going right with the timing I guess.
      
      Here is what happens:
      1. From the date picker in the form I chose this expiration date
      2. The Unix Timestamp I get is: 1609455600000
      3. The result I get from the query is 12/31/20 11:00 PM which is not correct. It should been 01/01/2021 11:00PM or event better 01/02/2021 1200 AM
      
      The DB timezone is Europe/Rome.
      
      Can you please help me in solving this?
      
      Thanks
      
      Marta
      Like • Show 1 Like1
      Actions
      - Ahmed Nofal @ Marta Benedetti on Dec 29, 2020 9:11 AM
        Mark Correct
        Correct Answer
        Hi Marta,
        
        1. Make sure that the following query returns the Rome time zone:
        select SESSIONTIMEZONE from dual;
        if it does return the expected time zone "Europe/Rome", try the following query which takes into consideration the time zone generically:
        select to_char((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone (select SESSIONTIMEZONE from dual),'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual;
        if it doesn't, try the following query (same as the above with the time zone hardcoded):
        select to_char((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone 'Europe/Rome','MM/DD/YY HH24:MI AM') as transformed_timestamp from dual;
        
        Epoch 1609455600000 is 01/01/2021 00:00 AM per the Europe/Rome time zone and you not using the time in the date field verifies this more as the time will be defaulted to 00:00 with each date picked from that date picker field.
        
        I used the following online converters as well to verify the results:
        https://www.epochconverter.com/ >> returns the GMT human date/time and also in your your PC's time zone
        Free Online Epoch And Unix Timestamp Converter - FreeFormatter.com >> returns the human date/time in directly to your PC's time zone.
        1 person found this helpful
        Like • Show 1 Like1
        Actions
        Marta Benedetti @ Ahmed Nofal on Dec 29, 2020 12:16 PM
        Mark Correct
        Correct Answer
        Hi Ahmed,
        
        now it's ok: I tried this expiration date 01/04/21 from the date picker and the value written on AD is: 132541884000000000 (in my time zone: January 4, 2021 12:00:00 AM GMT+01:00)
        I guess that now all I have to do in order to meet client's request is to add 24H so that the expiration date would be: January 5, 2021 12:00:00 AM GMT+01:00.
        
        Thanks for your help,
        
        Marta
        Like • Show 1 Like1
        Actions
        Ahmed Nofal @ Marta Benedetti on Dec 29, 2020 12:36 PM
        Unmark Correct
        Correct Answer
        Here are the queries with a 1 day or 24H addition to the originally picked date:
        
        Time zone generically fetched:
        select to_char(((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone (select SESSIONTIMEZONE from dual)) + interval '1' day,'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual
        
        Rome/Europe hardcoded time zone:
        select to_char((timestamp '1970-01-01 00:00:00 +00:00' + numtodsinterval(${PublicData_Onboarding_accountExpire}/1000,'SECOND')) at time zone 'Europe/Rome' + interval '1' day,'MM/DD/YY HH24:MI AM') as transformed_timestamp from dual
        1 person found this helpful
        Like • Show 2 Likes2
        Actions
        Marta Benedetti @ Ahmed Nofal on Dec 30, 2020 4:05 AM
        Mark Correct
        Correct Answer
        The sql query with time zone generically fetched is working perfectly fine.
        Thank you very much Ahmed for your help!
        Like • Show 0 Likes0
        Actions

SQL Date conversion from UNIX for Active Directory

Outcomes

Related Content

Recommended Content