Google Spanner Postgres DB fetch Last 5 hours data

Question

I have a table with 3 columns Id, Data, Date. Date has dataType timestamp with time zone.

I want to get last 5 hours created ids Query. I tried with interval query

 Select Id from table where
 date > (current_date - INTERVAL '5 hour')

It is showing interval is NOT supported in postgre

How I can query to get Id for last 5 Hour

Hi @Sneha Mule , I tried to reproduce the error that you came up with but I'm always querying successfully with the sample dataset and query you provide. Could you provide the specific error log when using the query above and also what version of PostgreSQL are you using? — Marc Anthony B, Feb 08 '23 at 22:44
@MarcAnthonyB were you testing on a native PostgreSQL database? The question is about a PostgreSQL interface database in Spanner not a standalone PostgreSQL database. — SQLmojoe, Feb 11 '23 at 00:30

score 0 · Answer 1 · answered Mar 07 '23 at 13:24

Yep, Cloud Spanner's PostgreSQL interface unfortunately doesn't yet support the INTERVAL data type.

But you can do so in your app. For example, if you're using Python on the application side, do something like:

from datetime import datetime, timedelta
t = datetime.now() - timedelta(hours=5)

Then for your query, do:

Select Id from table where date > $1

and pass t in as a query parameter. (Or as a datetime string-literal, but parameters are safer and faster if this is production code.)

1 Answers1