两个日期之间的差异postgresql在Excel格式 [英] Difference Between two dates postgresql In Excel Format
本文介绍了两个日期之间的差异postgresql在Excel格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello我想要显示两个日期之间的SQL差异:
* END DATE = dt_termino / now()
* START DATE = dt_inicio / p>
我有一个功能写入CSV ...但在我的csv输出如:
id_task_tarefa | dt_inicio | dt_termino | sum
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 01 22:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 01 22:09:52
我需要一种格式在Excel中输出[h]:mm:ss ; @
like this
id_task_tarefa | dt_inicio | dt_termino | sum
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 46:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 46:09:52
这是我的SQL:
SELECT id_task_tarefa,
dt_inicio,
dt_termino,
to_char(COALESCE(dt_termino :: timestamp,now():: timestamp)
- dt_inicio :: timestamp,'DD HH24:MI:SS')
从crm.task_interacao
解决方案
此功能可能方便您:
创建或替换函数interval_in_hours (interval)
返回文本语言sql作为$$
选择格式('%s:%s',
(extract(epoch from $ 1)/ 3600):: int,
to_char($ 1,'mi:ss'))
$$;
使用:
code> with the_data(id_task_tarefa,dt_inicio,dt_termino)as(
values
(211,timestamp'2016-01-25 10:40:25',timestamp'2016-01-27 08 :51:02'),
(210,timestamp'2016-01-25 10:40:29',timestamp'2016-01-27 08:50:21')
)
从the_data中选择*,interval_in_hours(dt_termino- dt_inicio):: interval作为sum
;
id_task_tarefa | dt_inicio | dt_termino | sum
---------------- + --------------------- + ------ --------------- + ----------
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 46:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 46:09:52
(2 rows)
Hello I want To Show SQL difference between two dates:
* END DATE = dt_termino / now()
* START DATE = dt_inicio
I have a Function to write in CSV... but in my csv ouput like:
id_task_tarefa | dt_inicio | dt_termino | sum
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 01 22:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 01 22:09:52
I need a format to output in Excel [h]:mm:ss;@ like this
id_task_tarefa | dt_inicio | dt_termino | sum
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 46:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 46:09:52
Here's My SQL:
SELECT id_task_tarefa,
dt_inicio,
dt_termino,
to_char(COALESCE(dt_termino::timestamp, now()::timestamp)
- dt_inicio::timestamp,'DD HH24:MI:SS')
FROM crm.task_interacao
解决方案
The function may be convenient for you:
create or replace function interval_in_hours(interval)
returns text language sql as $$
select format('%s:%s',
(extract (epoch from $1) / 3600)::int,
to_char($1, 'mi:ss'))
$$;
Use:
with the_data (id_task_tarefa, dt_inicio, dt_termino) as (
values
(211, timestamp '2016-01-25 10:40:25', timestamp '2016-01-27 08:51:02'),
(210, timestamp '2016-01-25 10:40:29', timestamp '2016-01-27 08:50:21')
)
select *, interval_in_hours(dt_termino- dt_inicio)::interval as sum
from the_data;
id_task_tarefa | dt_inicio | dt_termino | sum
----------------+---------------------+---------------------+----------
211 | 2016-01-25 10:40:25 | 2016-01-27 08:51:02 | 46:10:37
210 | 2016-01-25 10:40:29 | 2016-01-27 08:50:21 | 46:09:52
(2 rows)
这篇关于两个日期之间的差异postgresql在Excel格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文