我需要帮助将下面的配置单元查询转换为sql查询。我需要知道如何将unix时间戳函数转换成sql。

SELECT 
    person_id, supervisor_id, assignment_type, primary_flag, 
    effective_start_date, effective_end_date,
    MAX(effective_start_date) OVER (PARTITION BY p.person_id) AS max_effective_start_date
FROM 
    cg1_per_all_assignments_f p
WHERE 
    to_date(CURRENT_TIMESTAMP) >= COALESCE(to_date(from_unixtime(unix_timestamp(p.EFFECTIVE_START_DATE, 'yyyy-MM-dd'))), DATE_SUB(to_date(from_unixtime(unix_timestamp())), 1))
    AND to_date(CURRENT_TIMESTAMP) <= COALESCE(to_date(from_unixtime(unix_timestamp(p.EFFECTIVE_END_DATE, 'yyyy-MM-dd'))), DATE_ADD(to_date(from_unixtime(unix_timestamp())), 1))