将文件中的TIMESTAMP数据加载到云上的IBM Db2中

Question

我正在将数据加载到IBM云中，它的日期和时间格式如下，08/28/2004 05:50:56 PM。正确的时间格式是什么？我尝试过给定的可用格式，但它们不匹配。如果我需要创建一个，我该怎么做？非常感谢。

Answer 1

我相信云上Db2的Db2控制台当前使用LOAD语句将数据获取到Db2中。云上Db2仓库的Db2控制台同时使用LOAD和EXTERNAL TABLE(用于Netezza/PDA来源的数据(

LOAD的时间戳格式选项不如Db2 11.5 中基于EXTERNAL TABLE的加载所提供的选项灵活

你需要使用这种格式的

MM/DD/YYYY HH:MM:SS TT

根据本页timestampformat选项下列出的可用元素https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008305.html

下面是一个客户端命令行示例。

$ cat file.csv
08/28/2004 05:50:56 PM
$ db2 "create table ts(ts timestamp)"
DB20000I  The SQL command completed successfully.
$ db2 'import from file.csv of del modified by timestampformat="MM/DD/YYYY HH:MM:SS TT" insert into ts'
SQL3109N  The utility is beginning to load data from file "file.csv".
SQL3110N  The utility has completed processing.  "1" rows were read from the 
input file.
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".
SQL3222W  ...COMMIT of any database changes was successful.
SQL3149N  "1" rows were processed from the input file.  "1" rows were 
successfully inserted into the table.  "0" rows were rejected.

Number of rows read         = 1
Number of rows skipped      = 0
Number of rows inserted     = 1
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 1
$ db2 "select * from ts"
TS                        
--------------------------
2004-08-28-17.50.56.000000
1 record(s) selected.

Answer 2

以下作品：

values to_date('08/28/2004 05:50:56 PM', 'MM/DD/YYYY HH:MI:SS AM')

所以，试试这种格式。