CODEWITHSUNDEEP
sqlpostgresql
Manu
Manu

Reputation: 1120

Get elapsed/covered time in PostgreSQL

I have the following table:

CREATE TABLE my_table 
(
    the_visitor_id varchar(5) NOT NULL, 
    the_visitor_visit timestamp NOT NULL
)

INSERT INTO my_table
VALUES ('VIS01', '2019-05-02 09:00:00'), 
       ('VIS02', '2019-05-04 12:00:00'), 
       ('VIS03', '2019-05-06 18:30:00'), 
       ('VIS04', '2019-05-15 12:00:00'), 
       ('VIS05', '2019-06-30 18:00:00')

I want to retrieve the time elapsed/covered (from a reference point) with decimals by hour, day and week. For example, for VIS01, the time elapsed would be 0.375 (9/24 from 0:00:00), the day elapsed would be 0.04435 (1 day 9 hours makes 1.375 divided by 31 days is 0.04435 in May, the month of the visit) and the week elapsed would be 3.375 (3 days 0.375 hours from the Monday in the week).

For VIS05 the time elapsed is 0.75 (18 hours/24 hours), the day elapsed would be 0.9917 (29 days .75 covered hours makes 29.75 divided by 30 days of June) and the week elapsed would be 6.75 (6 days from Monday in a week that starts on Monday and finish on Sunday plus 0.75 hours)

This should be the result:

the_visitor_id    visitor_time_el    visitor_days_el    visitor_week_el
VIS01             0.375              0.0444             3.375
VIS02             0.5                0.1129             5.5
VIS03             0.7708             0.1539             0.7708
VIS04             0.5                0.4677             2.5
VIS05             0.75               0.9917             6.75

I've been stuck because I want to use this clause:

SELECT the_visitor_visit - date_trunc('month', the_visitor_visit) as visitor_days_el

But I don't quite get how I can convert that to decimals, and what to do with visitor_week_el. Please, any help will be greatly appreciated.

Upvotes: 1

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

This is basically using epoch to extract the seconds from various date differences:

select t.*,
       extract(epoch from the_visitor_visit::time) / (60 * 60 * 24) as visitor_time_el,
       (extract(epoch from (the_visitor_visit - date_trunc('month', the_visitor_visit))) / 
        extract(epoch from (date_trunc('month', the_visitor_visit) + interval '1 month' - date_trunc('month', the_visitor_visit)))
       ) as visitor_day_el,
       extract(epoch from (the_visitor_visit - date_trunc('week', the_visitor_visit))) / (60 * 60 * 24) as visitor_week_el
from my_table t

Here is a db<>fiddle.

Upvotes: 1

Related Questions