agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Ertan Küçükoðlu <ertan.kucukoglu@1nar.com.tr>
To: pgsql-sql@postgresql.org
Subject: Timestamp alculation identical to Microsoft Excel results
Date: Thu, 7 Dec 2017 12:59:27 +0300
Message-ID: <001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr> (raw)

Hello,

There is this Excel report which will be produced by an application. In
Excel, there is below equation
31/10/2017 15:05 - 31/10/2017 14:36:00 = 0:28:21
2017-10-31 13:22:17 - 2017-11-01 14:47:45 = 1/1/1900 01:25

That is very simple in PostgreSQL. Simply subtract two timestamp without
time zone fields and you have the result. However, Excel also represent that
result 0:28:21 as double notation 0.0196874999965075 and 1/1/1900 01:25 as
1,05935185185081.

I could not see any way to have same values using PostgreSQL query. I tried:
extract(epoch from time_field2) - extract(epoch from time_field1) and result
is 1701 and 5128 respectively.

Putting aside reasons as to why numbers are used instead of more human
understandable time format, I would like to learn if having same results as
Excel is possible.

Thanks & regards,
Ertan Küçükoðlu

 

view thread (3+ messages)  latest in thread

Message-ID: <001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr>
Permalink:  ../001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr/
Also on:    postgresql.org/message-id/001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ertan.kucukoglu@1nar.com.tr
  Subject: Re: Timestamp alculation identical to Microsoft Excel results
  In-Reply-To: <001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox