From Now to Current timestamp in Postgresql

Question

In mysql I am able to do this   SELECT   FROM table WHERE auth user lastactivity  gt  NOW   - 100   now in postgresql I am using this query   SELECT   FROM table WHERE auth user lastactivity  gt  CURRENT TIMESTAMP - 100   but I get this error   operator does not exist  timestamp with time zone - integer   How can I resolve

User · Answer

select   from table where column date  gt  now  - INTERVAL  6 hours

User · Answer

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 gt  SELECT NOW            - gt   2007-12-15 23 50 26  mysql gt  SELECT NOW     0          - gt  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 probably want to look at the date and interval types in pg

User · Answer

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

User · Answer

Use an interval instead of an integer   SELECT   FROM table WHERE auth user lastactivity  gt  CURRENT TIMESTAMP - INTERVAL  100 days

User · Answer

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

[sql] From Now() to Current_timestamp in Postgresql

Examples related to sql

Examples related to datetime

Examples related to postgresql