加载 CSV 时是否可以从 current_timestamp 默认值列设置 30 分钟的时间戳？

Question

我有一个时间戳字段 insert_time，它被定义为使用 CURRENT_TIMESTAMP 值自动更新。

如果我在将 civ 导入到 PostgresDB 时定义下面的值，它就可以正常工作。

1. 将时间戳列作为 csv 中的 now() 函数

2. 在 csv 中手动输入时间戳，例如 2013-08-08

作为当前条件，我想插入距离 current_timestamp 30 分钟的时间戳，所以我只需输入

NOW()-INTERVAL'30 MINUTES '，系统返回错误并包含以下语句。

InvalidDatetimeFormat：时间戳类型的输入语法无效： “(NOW() - 间隔‘30 分钟’)” 上下文：复制交易，第 1 行，插入时间列：“(NOW() - INTERVAL '30 MINUTES')”。

是否有任何解决方法可以让我简单地使用导入数据而无需更改代码库来实现此目的？

DB 表属性

CSV 示例：

c3f94bdf-8854-49d2-833d-0690eb821bdf,2290736458974872576,c3f94bdf-8854-49d2-833d-0690eb821bdf,10,40000000,BTC,2290736449804431425,2022030100,1646106066455,10,40000000,BTC,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290736443845317312,27729472,"19447008-1",A,BTC_USDT,BUY,1646106066455,2,600000,6,1000000,1646106066455,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd2,2290735086865954176,c3f94bdf-8854-49d2-833d-0690eb821bd2,10,40000000,ETH,2290735071284965826,2022030100,1646106025563,10,40000000,ETH,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290735065371963840,27727648,"19445792-0",A,ETH_USDT,BUY,1646106025563,2,1000000,6,1000000,1646106025563,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd3,2290728307695304704,c3f94bdf-8854-49d2-833d-0690eb821bd3,10,40000000,VET,2290728283028522497,2022030100,1646105823528,10,40000000,VET,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290728277231940960,27725824,"19444576-1",A,VET_USDT,BUY,1646105823528,2,1000000,6,1000000,1646105823528,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd4,2290722365598402272,c3f94bdf-8854-49d2-833d-0690eb821bd4,10,40000000,LUNA,2290722340448397249,2022030100,1646105646440,10,40000000,LUNA,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290722334488401536,27724000,"19443360-8",A,LUNA_USDT,BUY,1646105646440,2,1000000,6,1000000,1646105646440,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd5,2290589190301423232,c3f94bdf-8854-49d2-833d-0690eb821bd5,10,74800000,ADA,2290589095070535169,2022030100,1646101677507,10,74800000,ADA,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290589120065645888,22440128,"16738528-1",A,ADA_USDT,SELL,1646101677507,2,600000,6,1870000,1646101677507,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd6,2290584188362083520,c3f94bdf-8854-49d2-833d-0690eb821bd6,10,40000000,MANA,2290584186822704929,2022030100,1646101528437,10,40000000,MANA,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290584180756419392,22409120,"16708128-0",A,MANA_USDT,BUY,1646101528437,2,600000,6,1000000,1646101528437,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd7,2290582829397526912,c3f94bdf-8854-49d2-833d-0690eb821bd7,10,40000000,BUSD,2290582809255269282,2022030100,1646101487937,10,40000000,BUSD,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290582803356323808,22407296,"16706912-0",A,BUSD_USDT,BUY,1646101487937,2,1000000,6,1000000,1646101487937,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd8,2290579188400153888,c3f94bdf-8854-49d2-833d-0690eb821bd8,10,40000000,AAVE,2290579149684042434,2022030100,1646101379427,10,40000000,AAVE,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,USDT,2290579143738594816,22405472,"16705696-0",A,AAVE_USDT,BUY,1646101379427,2,1000000,6,1000000,1646101379427,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821bd9,2290576756852686496,c3f94bdf-8854-49d2-833d-0690eb821bd9,10,40000000,ETH,2290576726140179970,2022030100,1646101306961,10,40000000,ETH,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4000000000,CRO,2290576720287178368,22403648,"16704480-0",A,ETH_CRO,BUY,1646101306961,2,1000000,6,1000000,1646101306961,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'
c3f94bdf-8854-49d2-833d-0690eb821aa1,2290574614735494369,c3f94bdf-8854-49d2-833d-0690eb821aa1,10,40000000,MANA,2290574580787974626,2022030100,1646101243121,10,40000000,MANA,8,4000000000,USDT,c3f94bdf-8854-49d2-833d-0690eb821bde,8,4488000000,CRO,2290574606968299904,22401216,"16703072-1",A,MANA_CRO,SELL,1646101243121,2,600000,6,1870000,1646101243121,sample_trade,sample_trade,NOW() - INTERVAL '30 MINUTE'

数据导入源代码

@keyword("Import trade data to db")
def import_trade_data():
    _connection = init_database_connection()
    _nts_connection = init_nts_database_connection()

print(f'Connecting to {pg_host}:{pg_port} db {pg_db} with user {pg_user}')
import_file = f'{os.getcwd()}/data/import_trade_data.table'

cur = _connection.cursor()

if os.path.isfile(import_file):
    import_file1 = open(import_file, 'r')
    trade_tables = import_file1.readlines()

print('import data')
# import data
for data in trade_tables:
    data = str.rstrip(data)
    table_file = f'{os.getcwd()}/data/trade.csv'
    if os.path.isfile(table_file):
        print(f'loading {table_file}')
        f = open(table_file)
        cur.copy_from(file=f, table=data, sep=',')
        _connection.commit()
    else:
        print(f'Expected table file {table_file} not found')
write_to_console(f'Data is imported: {cur.rowcount}')

print('update data')

cur.execute(f"update exchange_order set source_sequence = 0 where status in ('ACTIVE', 'PENDING');")
cur.execute(f'update balance set source_sequence = 0 where source_sequence != 0;')
cur.execute(f'update event_source_sequence set last_sequence = 0;')
cur.execute(f'update event_source_sequence set related_id = 0;')
_connection.commit()

Answer 1

不可以。COPY 适用于数据，而不适用于表达式。 'now()' 是一个特殊情况的例外，但它不会扩展到其他表达式。