时间减法格式结果 [英] Time subtraction format result

问题描述

我无法格式化我的时间计算结果，也无法搜索论坛解决方案。我不希望查看领先结果（+09）的从UTC时间。
$ b $ pre $ $ $ c $ select
（localtimestamp - to_timestamp（us.STARTDATETIME，'hh24：mi：ss'））作为HoursPassed
从随机us

其中 us.STARTDATETIME 是一个 varchar2 ，类似于 08： 00

我的结果：

+09 07：30：17.160826

预期结果：

07:30:17

解决方案

当从另一个时间戳中减去一个结果是一个内部时间间隔的数据类型，但你可以把它as '每天的间隔时间：

 选择
（localtimestamp  -  to_timestamp（us.STARTDATETIME，'hh24：mi：ss'））作为HoursPassed 
 from random us; 
 
 HOURSPASSED 
 ------------------- 
 +08 15：26：54.293892 
  

'+08'（在我的会话时区）是天数，而不是UTC偏移量。那就是它的价值，因为当你将一个字符串转换为日期或时间戳并只提供时间部分时，日期部分就是 $ b

默认日期值是按如下方式确定的：

• 年份是当年，由SYSDATE返回。


• 月份是当前月份，由SYSDATE返回。


• 当天是01（每月的第一天）。


• 小时，分钟和秒都是0。
  
  
  

  这些默认值用于请求日期值的查询，其中日期本身不是指定...
  

所以我真的比较：

<$ p $从随机我们选择localtimestamp，to_timestamp（us.STARTDATETIME，'hh24：mi：ss'）
;

LOCALTIMESTAMP TO_TIMESTAMP（US.STARTDATET
-------------------------- ------ --------------------
2017-08-09 23：26：54.293892 2017-08-01 08：00：00.000000

您无法直接格式化时间间隔，但您可以提取时间元素并分别格式化这些元素，并将它们连接起来。

<$ （从本地时间戳
- to_timestamp（us.STARTDATETIME，'hh24：mi：ss'））），'FM00'）
| |'：'|| to_char（提取（从（本地时间戳
- to_timestamp（us.STARTDATETIME，'hh24：mi：ss'））），'FM00'）
||'：'| | to_char（提取（第二个从（本地时间戳
- to_timestamp（us.STARTDATETIME，'hh24：mi：ss'）））'FM00'）
以小时为单位
from random us;

HOURSPASSED
-----------
15:26:54


重复计算相同的时间间隔看起来有点浪费，难以管理，所以你可以用内联视图或CTE来实现：

<$ （
select localtimestamp - to_timestamp（us.STARTDATETIME，'hh24：mi：ss'）
from random us
）
select to_char（extract（小时差），'FM00'）
||'：'|| to_char（提取（从差异分钟），'FM00'）
||'：'|| to_char（提取（第二差异），'FM00'）
以小时为单位$ c $;

HOURSPASSED
-----------
15:26:54

您也可以使用日期而不是时间戳;减法然后给你的差异作为一个数字，整天和小数：

  select current_date  -  to_date（us.STARTDATETIME，' hh24：mi'）从随机我们经过
小时; 
 
 HOURSPASSED 
 ----------- 
 8.64368056 
  

格式化的最简单方法是将其添加到已知的午夜时间，然后使用 to_char（）：

  select to_char（date'1970-01-01'
 +（current_date  -  to_date（us.STARTDATETIME，'hh24：mi'））， 
'HH24：MI：SS'）从随机我们经过
小时; 
 
 HOURSPAS 
 -------- 
 15:26:54 
  

我坚持使用 current_date 作为与 localtimestamp 最接近的匹配项。你可能实际上需要 systimestamp 和/或 sysdate 。 （更多有关这里的区别。）

I'm having trouble formatting the result of my time calculation as well as searching for a forum solution. I do not wish to view the "hours from UTC" leading the result (the +09).

select
(localtimestamp - to_timestamp(us.STARTDATETIME,'hh24:mi:ss')) as HoursPassed
from random us

Where us.STARTDATETIME is a varchar2 with something like 08:00

My result:

+09 07:30:17.160826

Desired result:

07:30:17

解决方案

When you subtract one timestamp from another the result is an internal interval data type, but you can treat it as 'interval day to second':

select
(localtimestamp - to_timestamp(us.STARTDATETIME,'hh24:mi:ss')) as HoursPassed
from random us;

HOURSPASSED        
-------------------
+08 15:26:54.293892

The '+08' (in my session time zone) is the number of days, not a UTC offset; that is the value it is because when you convert a string to a date or timestamp and only provide the time part, the date part defaults to the first day of the current month:

The default date values are determined as follows:

• The year is the current year, as returned by SYSDATE.
• The month is the current month, as returned by SYSDATE.
• The day is 01 (the first day of the month).
• The hour, minute, and second are all 0.

These default values are used in a query that requests date values where the date itself is not specified ...

So I'm really comparing:

select localtimestamp, to_timestamp(us.STARTDATETIME,'hh24:mi:ss')
from random us;

LOCALTIMESTAMP             TO_TIMESTAMP(US.STARTDATET
-------------------------- --------------------------
2017-08-09 23:26:54.293892 2017-08-01 08:00:00.000000

You can't directly format an interval, but you can extract the elements of the time and format those separately, and concatenate them.

select to_char(extract(hour from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  ||':'|| to_char(extract(minute from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  ||':'|| to_char(extract(second from (localtimestamp
    - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss'))), 'FM00')
  as hourspassed
from random us;

HOURSPASSED
-----------
15:26:54

Repeatedly calculating the same interval looks a bit wasteful and hard to manage, so you can do that in an inline view or a CTE:

with cte (diff) as (
  select localtimestamp - to_timestamp(us.STARTDATETIME, 'hh24:mi:ss')
  from random us
)
select to_char(extract(hour from diff), 'FM00')
  ||':'|| to_char(extract(minute from diff), 'FM00')
  ||':'|| to_char(extract(second from diff), 'FM00')
  as hourspassed
from cte;

HOURSPASSED
-----------
15:26:54

You could also use dates instead of timestamps; subtraction then gives you the difference as a number, with whole and fractional days:

select current_date - to_date(us.STARTDATETIME, 'hh24:mi') as hourspassed
from random us;

HOURSPASSED
-----------
 8.64368056

The simplest way to format that is to add it to a known midnight time and then use to_char():

select to_char(date '1970-01-01'
  + (current_date - to_date(us.STARTDATETIME, 'hh24:mi')),
  'HH24:MI:SS') as hourspassed
from random us;

HOURSPAS
--------
15:26:54

I've stuck with current_date as the closest match to localtimestamp; you may actually want systimestamp and/or sysdate. (More on the difference here.)

这篇关于时间减法格式结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！