PostgreSQL查询中的日期列算术 [英] Date column arithmetic in PostgreSQL query

问题描述

我有两个看起来像这样的表：

I have two tables that look like this:

CREATE TABLE table1 (user_id int, the_date date);
CREATE TABLE table2 (user_id int, the_date date, something_else real);

我正在写一个看起来像这样的查询

I am writing a query that looks like this

CREATE TABLE foo AS 
 SELECT t1.user_id
 , (t1.the_date - (t2.the_date - t1.the_date)::int) start_date
 FROM table1 t1, table2 t2 
 where t1.user_id=t2.user_id
 ;

运行上述查询时，在psql控制台上显示以下错误：

When I run the above query, I get the following error displayed on the psql console:

ERROR:  syntax error at or near "$1"
LINE 1: ...the_date - (t2.the_date - t1.the_date)::int)  $1 ...

                                                             ^

查询结果的第二列将显示日期，该日期为 N天，比表1中的日期早，其中N是table2和table1中的日期之间的差（以天为单位）。

The second column in the query result is to show a date which is N days BEFORE the date in table1, where N is the difference (in days) between the date in table2 and table1.

注意： table2 的日期总是比 table1中的日期。

如何执行此日期计算并将结果存储为查询中的新列别名？

How can I perform this date calculation and store the result as a new column alias in my query?

我正在使用PG 8.4。

I am using PG 8.4.

推荐答案

您需要对进行表限定t1.user_id 消除歧义。加上其他调整：

You would need to table-qualify t1.user_id to disambiguate. Plus other adjustments:

CREATE TABLE foo AS 
SELECT user_id, (t1.the_date - (t2.the_date - t1.the_date)) AS start_date
FROM   table1 t1
JOIN   table2 t2 USING (user_id);

• 减去两个日期将得出整数。强制转换是多余的。

  • Subtracting two dates yields integer. Cast was redundant.

    不要为AS 关键字： //www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLE-ALIASES rel = nofollow noreferrer>列别名-通常可以省略 AS 获取表别名。 手册：

    Don't omit the AS keyword for column aliases - while it's generally OK to omit AS for table aliases. The manual:

    
    

    您可以省略 AS ，但前提是所需的输出名称与
    不匹配PostgreSQL关键字（参见附录C ）。为了防止将来可能添加
    关键字，建议始终
    要么写 AS 要么用双引号将输出名引起来。）

    You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.)

  
  

  • 带有 USING 子句的联接表仅保留 1 结果集中的连接列的实例（在这种情况下为 user_id ），您不必再进行表限定。

  • Joining tables with a USING clause only keeps one instance of the joining columns(s) (user_id in this case) in the result set and you don't have to table-qualify it any more.

    这篇关于PostgreSQL查询中的日期列算术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！