PostgreSQL get time difference between timestamps

Question

I've looked at many SO questions related to this but I can't seem to get anything to work. I'm not very good with semi complex SQL queries.

I want to get the difference between the current time and a column that is in unix timestamp in hours.

I'm not sure what I'm doing wrong or right for that matter. The goal is to only pull the rows that is less than 24 hours old. If there is a better way or example that works that would be great.

I tried several answers from here Timestamp Difference In Hours for PostgreSQL

I can't get this query to work no matter how many different ways I try it. wc.posted is a bigint store as unix timestamp

SELECT w.wal_id, wc.com_id, w.posted AS post_time, wc.posted AS com_time 
FROM wall as w LEFT JOIN wall_comments as wc ON w.wal_id=wc.wal_id 
WHERE (EXTRACT(EPOCH FROM wc.posted)) > current_timestamp - interval '24 hours'

Then the Error:

ERROR:  function pg_catalog.date_part(unknown, bigint) does not exist
LINE 1: ... wall_comments as wc ON w.wal_id=wc.wal_id WHERE (EXTRACT(EP...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function pg_catalog.date_part(unknown, bigint) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 148

Here is a simplified fiddle

score 2 · Accepted Answer · answered Jan 18 '14 at 05:33

2

From the fine manual:

A single-argument to_timestamp function is also available; it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

So to convert your bigint seconds-since-epoch to a timestampz:

to_timestamp(wc.posted)

Perhaps you're looking for this:

WHERE to_timestamp(wc.posted) > current_timestamp - interval '24 hours'

answered Jan 18 '14 at 05:33

mu is too short

426,620
70
833
800

Perfect. Thanks I guess that manual is pretty helpful. But I didn't quite understand that it needed to convert using that function. Thanks for pointing that out. – Panama Jack Jan 18 '14 at 05:45

score 1 · Answer 2 · answered Jan 18 '14 at 04:51

1

Try:

SELECT EXTRACT(EPOCH FROM (timestamp_B - timestamo_A))
FROM TableA

Details here: EXTRACT.

answered Jan 18 '14 at 04:51

Vignesh Kumar A

27,863
13
63
115

Please post your query in sql fiddle – Vignesh Kumar A Jan 18 '14 at 05:01

score 0 · Answer 3 · answered Jan 18 '14 at 05:45

0

Try this out i am sure this will help you

select field_1,field_2,field_3 from schema_name.table_name Where  
ROUND(EXTRACT(EPOCH FROM (cast(now() as timestamp)  - cast(your_time_field as timestamp)))/60) > 1440;

answered Jan 18 '14 at 05:45

smn_onrocks

1,282
1
18
33

Instead of using a cast function, you can also do this, `now():: timestamp - your_time_field:: timestamp` – Zoran777 Mar 31 '16 at 08:16

score 0 · Answer 4 · edited Aug 14 '23 at 19:35

The error message announce :

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Indeed, the simplest solution is to add an explicit timestamp type cast.

SELECT w.wal_id, wc.com_id, w.posted AS post_time, wc.posted AS com_time 
FROM wall as w LEFT JOIN wall_comments as wc ON w.wal_id=wc.wal_id 
WHERE (EXTRACT(EPOCH FROM wc.posted::timestamp)) > current_timestamp - interval '24 hours'

Notice the timestamp type cast :

wc.posted::timestamp

PostgreSQL get time difference between timestamps

4 Answers4