在PostgreSQL选择查询中将时间戳列值转换为纪元 [英] Convert timestamp column values to epoch in PostgreSQL select query
问题描述
我需要在 SELECT
查询中将时间戳值转换为纪元。
请找到以下样本表和预期结果。
样本表:
从日志中选择*;
id | 3
时间| 2016-03-30 18:44:19.189513
data1 |无
data3 |无
data4 | NONE
预期结果:
id | 3
时间| 1459343659
data1 |无
data3 |无
data4 | NONE
日志表的行数为n。请找到以下版本详细信息:
select version();
版本
------------------------------------- -------------------------------------------------- -------
x86_64-unknown-linux-gnu上的PostgreSQL 9.3.5,由gcc(Debian 4.7.2-5)4.7.2编译,64位
使用 extract()函数:
选择id,从时间中提取(时间段),
data1,data2,data3
从日志中;
显然,您的列不是时间戳列,而是 varchar
,因此您首先需要将其转换为实时时间戳,然后才能使用 extract()
选择id,
提取(时间从time :: timestamp开始的历元)作为时间,
data1,data2,data3
来自日志;
仅当该列中的所有值均具有正确的时间戳ISO格式时,此选项才起作用。 p>
这可以告诉您,您应该
从不保存日期, varchar
列中的时间戳或时间值!
I need to convert timestamp values to epoch in a SELECT
query.
Please find the below sample table and expected result.
Sample table:
select * from log;
id | 3
time | 2016-03-30 18:44:19.189513
data1 | NONE
data3 | NONE
data4 | NONE
Expected result:
id | 3
time | 1459343659
data1 | NONE
data3 | NONE
data4 | NONE
Log table is having n number of rows. Please find the below version details:
select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
Use the extract() function:
select id, extract(epoch from time) as time,
data1, data2,data3
from log;
Apparently your column is not a timestamp column but a varchar
, so you first need to cast that to a real timestamp before you can use extract()
select id,
extract(epoch from time::timestamp) as time,
data1, data2,data3
from log;
This will only work if all values in that column have the correct ISO format for a timestamp.
This teaches you, that you should
never store date, timestamp or time values in a varchar
column!
这篇关于在PostgreSQL选择查询中将时间戳列值转换为纪元的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!