From Now() to Current_timestamp in Postgresql
In mysql I am able to do this:
SELECT * FROM table WHERE auth_user.lastactivity > NOW() - 100
now in postgresql I am using this query:
SELECT * FROM table WHERE auth_user.lastactivity > CURRENT_TIMESTAMP - 100
but I get this error:
operator does not exist: timestamp with time zone - integer
How can I resolve ?
Use an interval instead of an integer:
SELECT * FROM table WHERE auth_user.lastactivity > CURRENT_TIMESTAMP - INTERVAL '100 days'
You can also use now() in Postgres. The problem is you can't add/subtract integers from timestamp or timestamptz. You can either do as Mark Byers suggests and subtract an interval, or use the date type which does allow you to add/subtract integers
SELECT now()::date + 100 AS date1, current_date - 100 AS date2
Here is what the MySQL docs say about NOW():
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
mysql> SELECT NOW(); -> '2007-12-15 23:50:26' mysql> SELECT NOW() + 0; -> 20071215235026.000000
Now, you can certainly reduce your smart date to something less...
SELECT ( date_part('year', NOW())::text || date_part('month', NOW())::text || date_part('day', NOW())::text || date_part('hour', NOW())::text || date_part('minute', NOW())::text || date_part('second', NOW())::text )::float8 + foo;
So the MySQL time essentially lets you treat NOW() as a dumber type, or it overrides + to make a presumption that I can't find in the MySQL docs. Eitherway, you probably want to look at the date and interval types in pg.
Here is an example ...
select * from tablename where to_char(added_time, 'YYYY-MM-DD') = to_char( now(), 'YYYY-MM-DD' )
added_time is a column name which I converted to char for match
select * from table where column_date > now()- INTERVAL '6 hours';