SQL Postgresql 中从 Now() 到 Current_timestamp
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2993468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
From Now() to Current_timestamp in Postgresql
提问by xRobot
In mysql I am able to do this:
在 mysql 我能够做到这一点：
SELECT *
FROM table
WHERE auth_user.lastactivity > NOW() - 100
now in postgresql I am using this query:
现在在 postgresql 我使用这个查询：
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 ?
我该如何解决？
回答by Mark Byers
Use an interval instead of an integer:
使用间隔而不是整数：
SELECT *
FROM table
WHERE auth_user.lastactivity > CURRENT_TIMESTAMP - INTERVAL '100 days'
回答by Scott Bailey
You can also use now()in Postgres. The problem is you can't add/subtract integers from timestampor timestamptz. You can either do as Mark Byers suggests and subtract an interval, or use the datetype which does allow you to add/subtract integers
您也可以now()在 Postgres 中使用。问题是您不能从timestamp或 中添加/减去整数timestamptz。您可以按照 Mark Byers 的建议做并减去一个区间，或者使用date允许您添加/减去整数的类型
SELECT now()::date + 100 AS date1, current_date - 100 AS date2
回答by Rohit Sonawane
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
added_time 是一个列名，我将其转换为 char 以进行匹配
回答by Evan Carroll
Here is what the MySQL docs say about NOW():
以下是 MySQL 文档的说明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.
以 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS.uuuuuu 格式的值返回当前日期和时间，具体取决于函数是在字符串还是数字上下文中使用。该值以当前时区表示。
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 typewith their own set of functionsand 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 dateand intervaltypes in pg.
因此，MySQL 时间基本上可以让您将其NOW()视为更笨的类型，或者它会覆盖+以做出我在 MySQL 文档中找不到的假设。无论哪种方式，您可能都想查看pg中的date和interval类型。
回答by Luis Carlos Herrera Santos
select * from table where column_date > now()- INTERVAL '6 hours';
