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

mu is too short · Accepted Answer · 2014-01-18 05:33:15Z

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, 2014 at 5:33

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panama Jack Over a year ago

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.

Vignesh Kumar A · Accepted Answer · 2014-01-18 04:51:55Z

1

Try:

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

Details here: EXTRACT.

answered Jan 18, 2014 at 4:51

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

1 Comment

Vignesh Kumar A Over a year ago

Please post your query in sql fiddle

smn_onrocks · Accepted Answer · 2014-01-18 05:45:27Z

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, 2014 at 5:45

smn_onrocks

1,3421 gold badge19 silver badges34 bronze badges

1 Comment

Zoran777 Over a year ago

Instead of using a cast function, you can also do this, now():: timestamp - your_time_field:: timestamp

DevThiman · Accepted Answer · 2023-08-14 19:35:13Z

0

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

edited Aug 14, 2023 at 19:35

DevThiman

1,1801 gold badge16 silver badges29 bronze badges

answered Feb 26, 2015 at 16:34

Rémi Becheras

15.3k14 gold badges55 silver badges85 bronze badges

Collectives™ on Stack Overflow

PostgreSQL get time difference between timestamps

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related