Convert epoch to date using DATEADD

Question

I explored a lot. I am using this to convert epoch time to date:

CASE 
  WHEN json_extract_path_text(cli, 'pickupDate') IS NULL OR json_extract_path_text(cli, 'pickupDate') = '' 
  THEN DATEADD(SECOND, CONVERT(bigint, json_extract_path_text(cli, 'pickupTime')), '1970-1-1'), '1970-1-1' ) 
  ELSE json_extract_path_text(cli, 'pickupDate') 
END AS "Start Date"

To give little context, if the key 'pickupDate' in json is not present, i need to make use of this key 'pickUpTime' to get the value. When i do this, i get this error:

Invalid operation: CASE types text and timestamp without time zone cannot be matched; 1 statement failed.

Can someone help me out here? How do we specify timezone or if there is another way to resolve this?

score 0 · Answer 1 · answered Sep 05 '19 at 09:32

Try breaking it down into parts to see where the issue could be.

Does this expression work?

DATEADD(SECOND, CONVERT(bigint, json_extract_path_text(cli, 'pickupTime')),'1970-1-1')

If it does, then try to find the resulting data types of the previous expression and this one to see if they match: json_extract_path_text(cli, 'pickupDate')

If that works, try explicitly declaring DATE '1970-1-1' as a date using the DATE prefix.

Also, not sure but it looks like there's an extra parentheses in the expression you posted above.

score 0 · Answer 2 · answered Sep 05 '19 at 09:34

The error message is quite clear - you can't return two incompatible data types from a case expression.

The case expression must return compatible data types from all it's branches, so it's not enough to simply read the text from the josn in the else part, you still need to convert it to datetime.
I'm not familiar with the specifics of Amazon redshift but I'm guessing you need convert

Convert epoch to date using DATEADD

2 Answers2