CODEWITHSUNDEEP
sqlpostgresqldateselecttimestamp
user12587364
user12587364

Reputation:

Postgtres: Calculate number of days as float considering hours/minutes

I would like to compute the number of days since 1899/12/30, taking into consideration the number of hours and minutes.

So, for example data in my table:

+--------------------+-------------+
|     dob            |    n_days   |
+--------------------+-------------+
|1980-08-09 13:34:10 |  29442.5654 |
|2005-12-15 23:10:00 |  38701.6528 |
|2020-02-26 15:56:00 |  43887.6639 |
+--------------------+-------------+

Query:

SELECT DATE_PART('day', dob -'1899-12-30 00:00:00'::TIMESTAMPTZ) AS n_days 
FROM my_date;

Returns only whole day count:

n_days
---------
29441
38701
43887

Upvotes: 2

Views: 248

Answers (1)

GMB
GMB

Reputation: 222402

Consider:

extract(epoch from dob - '1899-12-30 00:00:00'::timestamptz) / 60 / 60 / 24

Rationale:

  • the timestamp substraction gives you an interval that represents the difference between the timestamps

  • extract(epoch from ...) turns this to a number of seconds

  • all that is left to do is divide by the number of seconds that there is in a day

Demo on DB Fiddle:

with t as (
    select '1980-08-09 13:34:10'::timestamptz dob
    union all select '2005-12-15 23:10:00'::timestamptz
    union all select '2020-02-26 15:56:00'::timestamptz
)
select 
    dob,
    extract(epoch from dob - '1899-12-30 00:00:00'::timestamptz) / 60 / 60 / 24 as n_days
from t

dob                    | n_days            
:--------------------- | :-----------------
1980-08-09 13:34:10+01 | 29442.52372685185 
2005-12-15 23:10:00+00 | 38701.965277777774
2020-02-26 15:56:00+00 | 43887.66388888889

Upvotes: 3

Related Questions