CODEWITHSUNDEEP
sqlpostgresql
Hamza Khalid
Hamza Khalid

Reputation: 241

How to select date and time less than a timestamp?

I am making a join, and have to select rows where date and time is less than a particular date and time. I have a date and time column (combined) in my table, called 'hour'.

I'm trying to convert string to timestamp in the where clause (by using to_timestamp). The where clause if changed to a select query, stands perfectly in itself. But when used in my code (join), causes a syntax error.

Select 
t1.revenue,
t2.impressions

from t1
left join t2 on t1.id = t2.id

where t2.hour < to_timestamp('2019-07-24 23:59:59','YYYY-MM-DD HH24:MI:SS')

I get a syntax error near my 'where' clause.

Upvotes: 0

Views: 6166

Answers (2)

Martin Dimitrov
Martin Dimitrov

Reputation: 1304

Your issue is, you are missing a comma ',' in the SELECT clause.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

The syntax error is probably being caused by a missing comma in the SELECT clause. But beyond that, your query has other issues. Consider this updated version:

select 
    t1.revenue,
    t2.impressions
from t1
left join t2
    on t1.id = t2.id and
       t2.hour < '2019-07-24 23:59:59'::timestamp;

Given that your are doing a left join from t1 to t2, the restriction on t2 which currently appears in your WHERE clause most likely should be moved to the ON clause of the join. Not doing this would possibly mean filtering off records from t1 which would defeat the purpose of the left join.

Also note that you can just use ::timestamp to convert a string literal to a timestamp, though you may not even need to do this.

Upvotes: 2

Related Questions