Timestamp Difference in Hours for Postgresql

Timestamp Difference In Hours for PostgreSQL

The first things popping up

EXTRACT(EPOCH FROM current_timestamp-somedate)/3600

May not be pretty, but unblocks the road. Could be prettier if division of interval by interval was defined.

Edit: if you want it greater than zero either use abs or greatest(...,0). Whichever suits your intention.

Edit++: the reason why I didn't use age is that age with a single argument, to quote the documentation: Subtract from current_date (at midnight). Meaning you don't get an accurate "age" unless running at midnight. Right now it's almost 1am here:

select age(current_timestamp);
age
------------------
-00:52:40.826309
(1 row)

Postgresql timestamp difference greater than 1 hour

Just subtract the values and compare it with an interval

Select * 
from store
where exittime - entrytime > interval '1 hour';

This assumes that both columns are defined as timestamptz or timestamp

Time difference between two timestamps in postgres using age is not rounding up

Solved my issue using the following:

select to_char(AGE(DATE_TRUNC('second', date_completed::timestamp), DATE_TRUNC('second', date_started::timestamp)),'hh24:mi:ss') as "time_diff"
from my_table

Reference SO: Discard milliseconds part from timestamp

Just want to also acknowledge @Bohemian for their input in assisting me to solve this issue wrt microseconds.

Postgresql: How to find hours between 2 dates and 2 times?

Adding a time to a date yields a timestamp and subtracting one timestamp from another returns an interval.

So all you need to do is:

(enddate + endtime) - (startdate + starttime) as diff

An interval is nice in the context of SQL, but usually harder to handle in a programming language. You can easily convert an interval to seconds using extract(epoch from interval)

If you want to convert that to hours use extract and divide by 3600

extract(epoch from (enddate + endtime) - (startdate + starttime))/3600 as diff_hours

Check time difference between timestamp and now in postgres

Subtracting timestamps gives an interval. So you need to an interval or N minutes. Just change your last line.

UPDATE instances
SET status = 'STOPPED'
WHERE id = 1
AND health_check_at IS NOT NULL
AND localtimestamp - health_check_at > &N * interval '1 min';

Time difference within business hours

I'd suggest generating all the ranges of business hours between created_at and current_timestamp and summing them. While it is far from optimal solution in terms of performance I think it is the most straight-forward approach.
I use timestamp with time zone range tstzrange with intersection operator (*).

SELECT id,
created_at,
SUM(upper(business_range) - lower(business_range) ) business_hours
FROM (
SELECT id,
created_at,
tstzrange(date_in_range + '09:00'::time with time zone, date_in_range + '17:00'::time with time zone)
* tstzrange(created_at, current_timestamp) as business_range
FROM (
SELECT id,
created_at,
created_at::date + generate_series(0, current_timestamp::date - created_at::date) as date_in_range
FROM times
) dates_in_range
WHERE date_part('dow', date_in_range) in (1,2,3,4,5)
) business_hour_ranges
GROUP BY
id,
created_at

See the setup and example in db<>fiddle

Word of caution

While the implementation was made to be easy to understand and debug it may result in poor performance - especially if used with very old created_at dates. If that may be the case in your system, you may be better off writing function that checks the day of week of the created_at and current_date, finds the number of days between them and determines work hours.



Related Topics



Leave a reply



Submit