Timestamp alculation identical to Microsoft Excel results

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Timestamp alculation identical to Microsoft Excel results

Ertan Küçükoğlu
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

 




Reply | Threaded
Open this post in threaded view
|

Re: Timestamp alculation identical to Microsoft Excel results

Samed YILDIRIM
Hi Ertan,
 
I think following query works for you.
 
postgres=# select date_part('epoch', ('2017-11-01 14:47:45'::timestamp(0) - '2017-10-31 13:22:17'::timestamp(0))/3600/24);
 date_part
-----------
  1.059352
(1 row)
 
postgres=# select date_part('epoch', ( '2017-10-31 13:22:17'::timestamp(0)-'2017-11-01 14:47:45'::timestamp(0))/3600/24);
 date_part
-----------
 -1.059352
(1 row)
 
Best regards.
İyi çalışmalar.
Samed YILDIRIM
 
 
07.12.2017, 12:59, "Ertan Küçükoğlu" <[hidden email]>:

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





 

Reply | Threaded
Open this post in threaded view
|

Re: Timestamp alculation identical to Microsoft Excel results

Luuk
In reply to this post by Ertan Küçükoğlu
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


Previous Thread Next Thread