agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Luuk <luuk.vosslamber@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Timestamp alculation identical to Microsoft Excel results
Date: Thu, 7 Dec 2017 18:27:59 +0100
Message-ID: <20758a7c-051b-276f-3c9b-e106ab3f15ad@invalid.lan> (raw)
In-Reply-To: <xs4all.001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr>
References: <xs4all.001c01d36f42$114e5fb0$33eb1f10$@1nar.com.tr>

On 07-12-17 10:59, Ertan Küçükoğlu wrote:
> 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
> 

This is what the maker of excel has to say about that:
https://support.microsoft.com/en-us/help/214094/how-to-use-dates-and-times-in-excel

view thread (3+ messages)

Message-ID: <20758a7c-051b-276f-3c9b-e106ab3f15ad@invalid.lan>
Permalink:  ../20758a7c-051b-276f-3c9b-e106ab3f15ad@invalid.lan/
Also on:    postgresql.org/message-id/20758a7c-051b-276f-3c9b-e106ab3f15ad@invalid.lan

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: luuk.vosslamber@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Timestamp alculation identical to Microsoft Excel results
  In-Reply-To: <20758a7c-051b-276f-3c9b-e106ab3f15ad@invalid.lan>

* 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