Snowflake SQL query for JSON data unix time conversion

Question

Goal: 1) Ability to convert unix timestamp column labelled "PAYMENT.json_data:date_payment" into human readable format Ex. 01/02/20 2) and filter by the past 2 years. Unfortunately any code I try to maneuver results in errors such as "SQL compilation error: syntax error line 7 at position 0 unexpected 'PAYMENT' ". There is so much data that I can't analyze it after export nor will it export beyond 100MB.

SELECT
    PAYMENT.json_data:total,
    PAYMENT.json_data:date_payment,
    CUSTOMER.json_data:name
FROM PAYMENT
RIGHT JOIN CUSTOMER on customer.json_data:jnid=payment.json_data:customer



Limit 2
[![Output Sample][1]][1]

//All Payments in the system
//BROKEN    PAYMENT.json_data:DATE_FORMAT(DATE_ADD(FROM_date_payment(0), interval -315619200 second),'%Y-%m-%d');

Answer 1

with data as (
    select * 
    from values 
        (5671, 1399003200),
        (4500,1540580400) 
        v(total,date_payment)
)
select total, 
     date_payment, 
     to_timestamp(date_payment::number, 0) as datetime_type 
from data
--where datetime_type > '2018-01-01'
;

gives:

TOTAL   DATE_PAYMENT    DATETIME_TYPE
5671    1399003200  2014-05-02 04:00:00.000
4500    1540580400  2018-10-26 19:00:00.000

and when filtering:

TOTAL   DATE_PAYMENT    DATETIME_TYPE
4500    1540580400  2018-10-26 19:00:00.000

so you should use to_timestamp(, 0) for second based epoch values, and 3 for millisecond values.

then the other gotcha, when getting data out of json, is to make sure you cast it to number, otherwise the parameterized version can be lost..

then use the filter.

[edit] add explicit example columns.. and genuine fake JSON data

WITH payment AS (  
  SELECT parse_json(a) as json_data
  FROM VALUES 
    ('{"customer":1234, "total":5671, "date_payment": 1399003200 }' ),
    ('{"customer":"1234", "total":4500, "date_payment": "1540580400"}' ) 
    v(a)
), customer as (  
SELECT parse_json(b) as json_data
  FROM VALUES
    ('{"jnid":"1234", "name": "Company AAA"}'),
    ('{"jnid":1234, "name": "Company AAA"}')
    c(b)
)
SELECT
    p.json_data:total::number AS total
    ,p.json_data:date_payment::number AS date_payment
    ,c.json_data:name AS customer_name
    --,to_timestamp(p.json_data:date_payment::number, 0) as datetime_type 
FROM PAYMENT AS p
JOIN CUSTOMER AS c
  ON c.json_data:jnid = p.json_data:customer  
WHERE to_timestamp(p.json_data:date_payment::number, 0) >= '2018-07-21'
ORDER BY 3,2;