在PostgreSQL中的bigint和date之间进行比较的确切运算符是什么? [英] What's exactly operator to compare between `bigint` and `date` in postgresql?

问题描述

我的故事来自此:

我创建了一个函数创建动态分区表，它具有Table_Year_Month格式，例如table_2018_04，table_2018_05....创建分区函数的参数是bigint，例如create_partition_function(1518164237,1520583437) ;.之后，我将bigint转换为日期，以从时间戳获取 year 和 month .但是检查功能( check(timestamp >= date))不起作用

I created a function create dynamic partition table with Table_Year_Month format such as table_2018_04, table_2018_05 .... The arguments of creation partition function are bigint such as create_partition_function(1518164237,1520583437) ;. After that I convert bigint to date to can get year and month from timestamp. But the check function ( check(timestamp >= date)) don't work

我无法在sql中比较bigint> = date.什么运营商可以比较他们的?

I can't compare bigint >= date in sql. What's operator can compare their?

我尝试使用sql的UNIX_TIMESTAMP函数将时间戳转换为日期时间，但是不起作用

I tried convert timestamp to datetime with UNIX_TIMESTAMP function of sql but don't work

CREATE OR REPLACE FUNCTION create_partition_function( DATE, DATE )
returns void AS $$
DECLARE
    create_query text;
    index_query text;
BEGIN
    FOR create_query, index_query IN SELECT
            'create table user_event_firebase_'
            || TO_CHAR( d, 'YYYY_MM' )
            || ' ( check( timestamp >= UNIX_TIMESTAMP(date,'%Y %M %D' )'''
            || TO_CHAR( d, 'YYYY-MM-DD' )
            || ''' and timestamp < UNIX_TIMESTAMP(date,'%Y %M %D' ) '''
            || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
            || ''' ) ) inherits ( user_event_firebase );',
            'create index user_event_firebase_'
            || TO_CHAR( d, 'YYYY_MM' )
            || '_time on user_event_firebase_' 
            || TO_CHAR( d, 'YYYY_MM' )
            || ' ( timestamp );'
        FROM generate_series( to_timestamp($1), to_timestamp($2), '1 month'::interval ) AS d
    LOOP
        EXECUTE create_query;
        EXECUTE index_query;
    END LOOP;
END;
$$
language plpgsql;

p/s:bigint和date是sql中的数据类型.

p/s : bigint and dateare data type in sql.

ERROR:  invalid input syntax for integer: "2018-02-09"
CONTEXT:  SQL statement "create table user_event_firebase_2018_02 ( check( timestamp >= bigint '2018-02-09' and timestamp < bigint '2018-03-09' ) ) inherits ( user_event_firebase );"
PL/pgSQL function create_partition_function(date,date) line 21 at EXECUTE

推荐答案

您可以使用

SELECT extract(epoch from '2016-05-03'::date)
--result: 1462233600

SELECT to_timestamp(1462233600)::date;
--result: '2016-05-03'

这篇关于在PostgreSQL中的bigint和date之间进行比较的确切运算符是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！