SQL 如何在 Oracle 中插入时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5420833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert a timestamp in Oracle?
提问by Mike Rifgin
I have an Oracle DB with a timestampfield in it. What is the correct SQL code to insert a timestampinto this field?
我有一个带有timestamp字段的 Oracle 数据库。将 atimestamp插入该字段的正确 SQL 代码是什么？
回答by reggie
insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));
if you want the current time stamp to be inserted then:
如果要插入当前时间戳，则：
insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);
回答by Quassnoi
INSERT
INTO mytable (timestamp_field)
VALUES (CURRENT_TIMESTAMP)
CURRENT_TIMESTAMPand SYSTIMESTAMPare Oracle reserved words for this purpose. They are the timestamp analog of SYSDATE.
CURRENT_TIMESTAMP和SYSTIMESTAMP是为此目的的 Oracle 保留字。它们是 的时间戳模拟SYSDATE。
回答by Kash
INSERT INTO TABLE_NAME (TIMESTAMP_VALUE) VALUES (TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
回答by Dave Costa
Kind of depends on where the value you want to insert is coming from. If you want to insert the current time you can use CURRENT_TIMESTAMPas shown in other answers (or SYSTIMESTAMP).
这取决于您要插入的值的来源。如果要插入当前时间，可以使用CURRENT_TIMESTAMP其他答案（或SYSTIMESTAMP）中所示的方法。
If you have a time as a string and want to convert it to a timestamp, use an expression like
如果您将时间作为字符串并希望将其转换为时间戳，请使用类似的表达式
to_timestamp(:timestamp_as_string,'MM/DD/YYYY HH24:MI:SS.FF3')
The time format components are, I hope, self-explanatory, except that FF3means 3 digits of sub-second precision. You can go as high as 6 digits of precision.
我希望时间格式组件是不言自明的，除了这FF3意味着 3 位亚秒级精度。您可以达到 6 位数的精度。
If you are inserting from an application, the best answer may depend on how the date/time value is stored in your language. For instance you can map certain Java objects directly to a TIMESTAMPcolumn, but you need to understand the JDBCtype mappings.
如果您从应用程序插入，最佳答案可能取决于日期/时间值如何以您的语言存储。例如，您可以将某些 Java 对象直接映射到TIMESTAMP列，但您需要了解JDBC类型映射。
回答by a_horse_with_no_name
I prefer ANSI timestamp literals:
我更喜欢 ANSI 时间戳文字：
insert into the_table
(the_timestamp_column)
values
(timestamp '2017-10-12 21:22:23');
More details in the manual: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062
手册中的更多详细信息：https: //docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062
回答by Sai Krishna
Inserting date in sql
在sql中插入日期
insert
into tablename (timestamp_value)
values ('dd-mm-yyyy hh-mm-ss AM');
If suppose we wanted to insert system date
如果假设我们想插入系统日期
insert
into tablename (timestamp_value)
values (sysdate);
回答by Mario The Spoon
For my own future reference:
供我将来参考：
With cx_Oracle use cursor.setinputsize(...):
随着 cx_Oracle 使用 cursor.setinputsize(...)：
mycursor = connection.cursor();
mycursor.setinputsize( mytimestamp=cx_Oracle.TIMESTAMP );
params = { 'mytimestamp': timestampVar };
cusrsor.execute("INSERT INTO mytable (timestamp_field9 VALUES(:mytimestamp)", params);
No converting in the db needed. See Oracle Documentation
不需要在数据库中进行转换。请参阅Oracle 文档
回答by Ahuramazda
First of all you need to make the field Nullable, then after that so simple - instead of putting a value put this code CURRENT_TIMESTAMP.
首先，您需要将字段设为 Nullable，然后就这么简单 - 而不是放置一个值，而是放置此代码CURRENT_TIMESTAMP。
回答by Else
One can simply use
一个可以简单地使用
INSERT INTO MY_TABLE(MY_TIMESTAMP_FIELD)
VALUES (TIMESTAMP '2019-02-15 13:22:11.871+02:00');
This way you won't have to worry about date format string, just use default timestamp format.
这样您就不必担心日期格式字符串，只需使用默认时间戳格式即可。
Works with Oracle 11, have no idea if it does for earlier Oracle versions.
适用于 Oracle 11，不知道它是否适用于早期的 Oracle 版本。
回答by VGuest
CREATE TABLE Table1 (
id int identity(1, 1) NOT NULL,
Somecolmn varchar (5),
LastChanged [timestamp] NOT NULL)
this works for mssql 2012
这适用于 mssql 2012
INSERT INTO Table1 VALUES('hello',DEFAULT)
