agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedTimestamp alculation identical to Microsoft Excel results
3+ messages / 3 participants
[nested] [flat]
* Timestamp alculation identical to Microsoft Excel results
@ 2017-12-07 09:59 =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-12-07 10:15 ` Re: Timestamp alculation identical to Microsoft Excel results Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 3+ messages in thread
From: Ertan Küçükoðlu @ 2017-12-07 09:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Timestamp alculation identical to Microsoft Excel results
2017-12-07 09:59 Timestamp alculation identical to Microsoft Excel results =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
@ 2017-12-07 10:15 ` Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 3+ messages in thread
From: Samed YILDIRIM @ 2017-12-07 10:15 UTC (permalink / raw)
To: Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.tr>; pgsql-sql
<div>Hi Ertan,</div><div> </div><div>I think following query works for you.</div><div> </div><div><div>postgres=# select date_part('epoch', ('2017-11-01 14:47:45'::timestamp(0) - '2017-10-31 13:22:17'::timestamp(0))/3600/24);</div><div> date_part</div><div>-----------</div><div> 1.059352</div><div>(1 row)</div><div> </div><div>postgres=# select date_part('epoch', ( '2017-10-31 13:22:17'::timestamp(0)-'2017-11-01 14:47:45'::timestamp(0))/3600/24);</div><div> date_part</div><div>-----------</div><div> -1.059352</div><div>(1 row)</div><div> </div><div>Best regards.</div></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div>07.12.2017, 12:59, "Ertan Küçükoğlu" <ertan.kucukoglu@1nar.com.tr>:</div><blockquote type="cite"><p>Hello,<br /><br />There is this Excel report which will be produced by an application. In<br />Excel, there is below equation<br />31/10/2017 15:05 - 31/10/2017 14:36:00 = 0:28:21<br /><span>2017-10-31 13</span>:22:<span>17 - 2017-11-01 14</span>:47:45 = 1/1/1900 01:25<br /><br />That is very simple in PostgreSQL. Simply subtract two timestamp without<br />time zone fields and you have the result. However, Excel also represent that<br />result 0:28:21 as double notation 0.<span>0196874999965075</span> and 1/1/1900 01:25 as<br />1,<span>05935185185081</span>.<br /><br />I could not see any way to have same values using PostgreSQL query. I tried:<br />extract(epoch from time_field2) - extract(epoch from time_field1) and result<br />is 1701 and 5128 respectively.<br /><br />Putting aside reasons as to why numbers are used instead of more human<br />understandable time format, I would like to learn if having same results as<br />Excel is possible.<br /><br />Thanks & regards,<br />Ertan Küçükoğlu<br /><br /><br /><br /><br /><br /> </p></blockquote>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Timestamp alculation identical to Microsoft Excel results
@ 2017-12-07 17:27 Luuk <luuk.vosslamber@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Luuk @ 2017-12-07 17:27 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-12-07 17:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-07 09:59 Timestamp alculation identical to Microsoft Excel results =?iso-8859-9?B?RXJ0YW4gS/zn/Gtv8Gx1?= <ertan.kucukoglu@1nar.com.tr>
2017-12-07 10:15 ` Samed YILDIRIM <samed@reddoc.net>
2017-12-07 17:27 Re: Timestamp alculation identical to Microsoft Excel results Luuk <luuk.vosslamber@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox