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
SQL - Call Stored Procedure for Each Record
Get Avg Ignoring Null or Zero Values
How to Get a List of Element Names from an Xml Value in SQL Server
MySQL Subquery Returns More Than One Row
Move Cells Left in SQL If Left Contains Null and Right Contains Value
Where Clause to Find All Records in a Specific Month
Script Entire Database SQL-Server
How to Convert Unix Epoch Time in SQLite
SQL 'Like' Query Using '%' Where the Search Criteria Contains '%'
Inserting into Oracle and Retrieving the Generated Sequence Id
How to Insert Unicode Text to SQL Server from Query Window
Base 36 to Base 10 Conversion Using SQL Only
Function-Based Indexes in SQL Server
Is Id Column Position in Postgresql Important
How to Create a New Database with the Hstore Extension Already Installed