如何在 SQL 中将时间戳（日期格式）转换为 BIGINT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13477408/
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 convert a timestamp (DATE FORMAT) to BIGINT in SQL
提问by developer1
I need to convert the timestamp value (Format: 2012-11-19 14:29:50.0) to a BIGINTvalue (Format: 2012111315041650= YYYYMMDDhhmmss). I need to insert current time in a column of a table which accepts only BIGINT.
我需要将时间戳值（格式：）转换2012-11-19 14:29:50.0为BIGINT值（格式：2012111315041650= YYYYMMDDhhmmss）。我需要在表的一列中插入当前时间，该列只接受BIGINT.
I am using Squirrel SQL Client Version 3.3.0. The query I am using right now is
我正在使用 Squirrel SQL 客户端版本 3.3.0。我现在使用的查询是
INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE)
VALUES (2012111315041650, CURRENT_TIMESTAMP);
Instead of manually entering the BIGINT_DATEvalue, I want to convert the CURRENT_TIMESTAMPor NOW()to a BIGINTvalue as the format YYYYMMDDHHMISS
BIGINT_DATE我想将CURRENT_TIMESTAMPor转换NOW()为BIGINT值作为格式，而不是手动输入值YYYYMMDDHHMISS
Something like
就像是
INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE)
VALUES ("CONVERT(CURRENT_TIMESTAMP,BIGINT)", CURRENT_TIMESTAMP);
Let me know if it is feasible to do this way
让我知道这样做是否可行
Please help me with this.
请帮我解决一下这个。
Thanks
谢谢
回答by a_horse_with_no_name
For Postgres you can use:
对于 Postgres，您可以使用：
INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE)
VALUES (to_char(current_timestamp, 'yyyymmddhh24miss')::bigint, CURRENT_TIMESTAMP);
回答by Manuel
To convert timestamp values to bigint values (in seconds) you can do it in this way:
要将时间戳值转换为 bigint 值（以秒为单位），您可以通过以下方式进行：
SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2019-04-02T14:56:39.009'))::bigint -- -> 1554216999
To convert timestamp values to bigint values (in millis) you can do it in this way:
要将时间戳值转换为 bigint 值（以毫秒为单位），您可以通过以下方式进行：
SELECT ((EXTRACT(EPOCH FROM TIMESTAMP '2019-04-02T14:56:39.009'))::numeric * 1000)::bigint -- -> 1554216999009
If you want to convert all timestamp values of a column into a table into another column of the same table you can run this query (result in millis):
如果要将一列的所有时间戳值转换为表中的另一列，则可以运行此查询（结果为毫秒）：
UPDATE $table_name$ SET $column_with_values_bigint$ = (EXTRACT(EPOCH FROM $column_with_values_timestamp$)::numeric * 1000)::bigint
Hope this help.
希望这有帮助。
回答by VolkanCetinkaya
DATETIME to BIGINT;
日期时间到 BIGINT；
SELECT CONVERT(BIGINT, FORMAT(CURRENT_TIMESTAMP, 'yyyyMMddHHmmss'))
OR
或者
SELECT CONVERT(BIGINT, FORMAT(GETDATE(), 'yyyyMMddHHmmss'))
BIGINT to DATETIME;
BIGINT 到日期时间；
SELECT CONVERT(DATETIME, FORMAT(20191220213340, '####-##-## ##:##:##'))
回答by whati001
you can try something like that on MS SQL:
您可以在 MS SQL 上尝试类似的操作：
From bigint to datetime:
从 bigint 到 datetime：
select dateadd(s, convert(bigint, '<bigint value like 1477958400000>') / 1000, convert(datetime, '1-1-1970 00:00:00'))
From datetime to bigint:
从日期时间到 bigint：
select cast(datediff(s, convert(datetime, '1-1-1970 00:00:00'), convert(datetime, '<datetime value like 11-01-2016>')) as bigint)* 1000
Cheers
干杯
回答by Yogendra Singh
For Oracle: Use to_charand to_numberfunctions as below
对于 Oracle：使用to_char和to_number功能如下
TO_NUMBER(TO_CHAR('2012-11-19 14:29:50.0', 'YYYYMMDDHHMISS'))
For MySQL: Use DATE_FORMAT& CONVERTas below:
对于 MySQL：使用DATE_FORMAT&CONVERT如下：
CONVERT(DATE_FORMAT('2012-11-19 14:29:50.0'', '%Y%M%d%H%i%s'), UNSIGNED BIGINT)
回答by bendataclear
The following will give you what (I think) you are after:
以下内容将为您提供（我认为）您所追求的内容：
DECLARE @TM VARCHAR(50) = '2012-11-19 14:29:50.0'
SELECT (CAST(SUBSTRING(@TM,1,4) AS INT) * 10000000000)
+ (CAST(SUBSTRING(@TM,6,2) AS INT) * 100000000)
+ (CAST(SUBSTRING(@TM,9,2) AS INT) * 1000000)
+ (CAST(SUBSTRING(@TM,12,2) AS INT)* 10000)
+ (CAST(SUBSTRING(@TM,15,2) AS INT)* 100)
+ (CAST(SUBSTRING(@TM,18,2) AS INT))
However there may be a better solution to the overall problem which others have mentioned, if you give some more details we might be able to suggest a less dirty alternative.
但是，其他人提到的整体问题可能有更好的解决方案，如果您提供更多详细信息，我们可能会建议一个不那么脏的替代方案。
回答by Kaf
Sql Server; Replace GETDATE()With your Column name
数据库服务器；替换为GETDATE()您的Column name
SELECT CONVERT(bigint,
CONVERT(VARCHAR(12) , GETDATE() ,112) +
REPLACE(CONVERT(VARCHAR(8) , GETDATE() ,108),':',''))
