PostgreSQL中epoch的用法

epoch的含义官网上的原本解释如下：

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative);
for interval values, the total number of seconds in the interval.

翻译过来就是说，对于日期和时间戳类型的值，会获取到从1970-01-01 00:00:00 UTC这个Linux纪元年的开始时间到给定的日期或者时间戳参数的时间之间相隔的秒数。

select extract('epoch' from '2020-02-20 11:11:11'::timestamp)
输出 1582168271

而对于interval这种时间间隔类型，这会获取到这个时间间隔对应的秒数目

select extract('epoch' from interval '10 YEAR') # 有year month days hour minute second
输出 315576000 

今天是 2020-7-21
select extract('epoch' from age(cast('2020-07-20 00:00:00' as timestamp)))
select extract('epoch' from age('2020-07-20 00:00:00'::timestamp))
输出 86400

在sequelize 中的使用

// attributes:{include: []}选择所有模型属性并添加一些附加值

let rooms = await RoomModel.findAndCountAll({
            'attributes': {
                'include': [[ Sequelize.literal('EXTRACT(epoch FROM CAST( "room"."msgAt" AS TIMESTAMP)) - EXTRACT(epoch FROM CAST( "lookInfo"."outAt" AS TIMESTAMP))'), 'siun' ]],
            },
            'where': {
                'user_hashs': {
                    '$overlap': [ user_hash ],
                },
            },
            'limit': limit,
            'offset': offset,
            'order': [[ 'msgAt', 'desc' ], [ Sequelize.literal('"siun" asc nulls last') ]],
        });