From Now() to Current_timestamp in Postgresql

Question

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 ?

Answer 1

Use an interval instead of an integer:

SELECT *
FROM table
WHERE auth_user.lastactivity > CURRENT_TIMESTAMP - INTERVAL '100 days'

Answer 2

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;

But, that would be a really bad idea, what you need to understand is that times and dates are not stupid unformated numbers, they are their own type with their own set of functions and operators

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 want to look the date and integral types in pg.

Answer 3

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 suggest 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