I am parsing a json array and one field I am pulling out is closedate. However closedate has two different date formats one is YYYY-MM-DD and the other is a 13 digit timestamp. I am trying to get consistent formatting of the dates as well as have it be an integer compared to a string. Right now the query returning the close date is:

json_array_elements(ld.data->'Table1'->'Details')->>'closeDate' as closedate

and it returns close date as a string:

Someone was telling me to do something like a case statement with regex. But I am not familiar with regex function. Any help is appreciated.

CodePudding user response：

An example of one way to make this work. My regex skills are not strong so others may have a better solution:

create table regex_test (id int, fld_1 varchar);
insert into  regex_test values (1, '1606824000000'), (2, '2021-09-29');

 select * from regex_test ;
 id |     fld_1     
---- ---------------
  1 | 1606824000000
  2 | 2021-09-29

select 
    id, 
    case when fld_1 ~* '^[0-9]*$' then 
        to_timestamp(fld_1::bigint/1000) 
    else 
        fld_1::timestamp end as ts_fld 
from 
    regex_test;
 id |         ts_fld         
---- ------------------------
  1 | 2020-12-01 04:00:00-08
  2 | 2021-09-29 00:00:00-07

CodePudding user response：

I hope this query help you

with data as (
  select
    json_array_elements(data->'Table1'->'Details')->>'closeDate' as closedate 
  from your_table
)
select 
  case when closedate::text ~ '^[0-9] $' then 
    to_timestamp(closedate::numeric / 1000)::date 
  else 
    closedate::date 
  end 
from data;