amazon-redshift - 如何创建日期的纪元列表

Question

我正在使用下面的代码生成纪元中的日期列表。但是，我希望列表中的最新日期等于 unix_timestamp() 的值。

-- generate list of id's
with t1 as
(
select row_number() over (order by order_id) as n from orders 
limit 2000 
),
-- generate date based on incremental id's
t2 as 
(select 
  (getdate() - n+1) as date 
 from t1
)
  select 
    extract(epoch from date) as date
  from t2 
  where t2.date >= '2014-01-01'
  order by date desc

我不确定如何将此列表中的最后一个值设为 unix_timestamp()

标签： amazon-redshift

Answer 1

使用提取的时代报价

select extract('epoch' from timestamp date) as date
from t2 
where t2.date >= '2014-01-01'
order by date desc

没有使用 date_part 的引号

select date_part(epoch, date) as date
from t2 
where t2.date >= '2014-01-01'
order by date desc