SQL 从 ORACLE 的 TimeStamp 列中提取时间部分

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2951858/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:25:26  来源:igfitidea点击:

Extract time part from TimeStamp column in ORACLE

sqloracle

提问by RRUZ

Currently I'm using MyTimeStampField-TRUNC(MyTimeStampField)to extract the time part from a timestamp column in Oracle.

目前我正在使用MyTimeStampField-TRUNC(MyTimeStampField)从 Oracle 中的时间戳列中提取时间部分。

SELECT CURRENT_TIMESTAMP-TRUNC(CURRENT_TIMESTAMP) FROM DUAL

This returns

这返回

+00 13:12:07.100729

+00 13:12:07.100729

This works OK for me, to extract the time part from a timestamp field, but I'm wondering if there is a better way (may be using a built-in function of ORACLE) to do this?

这对我来说没问题,从时间戳字段中提取时间部分,但我想知道是否有更好的方法(可能使用 ORACLE 的内置函数)来做到这一点?

采纳答案by Frank Heikens

What about EXTRACT()function?

怎么样提取物()函数?

回答by LBushkin

You could always do something like:

你总是可以做这样的事情:

select TO_DATE(TO_CHAR(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss') from dual

I believe this will work with timestamps as well.

我相信这也适用于时间戳。

回答by Sagar

select TO_DATE(TO_CHAR(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss') from dual

This gives the timestamp for 1hour less than the actual.

这给出了比实际少 1 小时的时间戳。

回答by Art

This may help:

这可能有帮助:

Select EXTRACT(HOUR FROM (SYSDATE - trunc(sysdate)) DAY TO SECOND ) From dual;

回答by Amir Md Amiruzzaman

You want just date then use

你只想要约会然后使用

to_char(cast(SYSDATE as date),'DD-MM-YYYY')

and if you want just time then use

如果你只想要时间然后使用

to_char(cast(SYSDATE as date),'hh24:mi:ss')

the parameters are making all the changed

参数正在使所有更改

'DD-MM-YYYY'

and

'hh24:mi:ss'

回答by Vadzim

I think the method in question is shorter and faster than in both answers. Cause it involves just two math operations and no complex parsing, formatting and conversion.

我认为所讨论的方法比两个答案都更短、更快。因为它只涉及两个数学运算,没有复杂的解析、格式化和转换。

回答by Monty

select hour(CURRENT_TIMESTAMP)