如何在日期时间列上连接 postgresql 中的两个表，并在一个列中显示所有日期时间寄存器？

我有两个表：

TABLE_MACHINE_1

datetime,            sensor_1
2017-10-01 21:00:00, 1.18047
2017-10-01 21:15:00, 1.18023
2017-10-01 21:30:00, 1.18059
2017-10-01 21:45:00, 1.18067
2017-10-01 22:00:00, 1.18056
2017-10-01 22:15:00, 1.18147
2017-10-01 22:30:00, 1.18104
2017-10-01 22:45:00, 1.18111
2017-10-01 23:00:00, 1.18103
2017-10-01 23:15:00, 1.18072
2017-10-01 23:30:00, 1.18083
2017-10-01 23:45:00, 1.18079
2017-10-02 00:00:00, 1.17996
2017-10-02 00:15:00, 1.17987
2017-10-02 00:30:00, 1.17968
2017-10-02 00:45:00, 1.17893
2017-10-02 01:00:00, 1.17852
2017-10-02 01:15:00, 1.17833

TABLE_MACHINE_2

datetime,            sensor_2
2017-10-01 21:45:00, 112.1
2017-10-01 22:00:00, 133.2
2017-10-01 22:15:00, 143.5
2017-10-01 22:30:00, 154.9
2017-10-01 22:45:00, 166.1
2017-10-01 23:30:00, 123.1
2017-10-01 23:45:00, 134.1
2017-10-02 00:00:00, 188.3
2017-10-02 01:00:00, 192.9
2017-10-02 01:15:00, 105.3
2017-10-02 01:30:00, 172.9
2017-10-02 01:45:00, 145.3
2017-10-02 02:00:00, 174.9
2017-10-02 02:15:00, 155.3

我预计结果如何：

加入预期

datetime,            sensor_1  sensor_2
2017-10-01 21:00:00, 1.18047,  NULL
2017-10-01 21:15:00, 1.18023,  NULL
2017-10-01 21:30:00, 1.18059,  NULL
2017-10-01 21:45:00, 1.18067,  112.1
2017-10-01 22:00:00, 1.18056,  133.2
2017-10-01 22:15:00, 1.18147,  143.5
2017-10-01 22:30:00, 1.18104,  154.9
2017-10-01 22:45:00, 1.18111,  166.1
2017-10-01 23:00:00, 1.18103,  NULL
2017-10-01 23:15:00, 1.18072,  NULL
2017-10-01 23:30:00, 1.18083,  123.1
2017-10-01 23:45:00, 1.18079,  134.1
2017-10-02 00:00:00, 1.17996,  188.3
2017-10-02 00:15:00, 1.17987,  105.3
2017-10-02 00:30:00, 1.17968,  NULL
2017-10-02 00:45:00, 1.17893,  NULL
2017-10-02 01:00:00, 1.17852,  NULL
2017-10-02 01:15:00, 1.17833,  NULL
2017-10-02 01:30:00, NULL   ,  172.9
2017-10-02 01:45:00, NULL   ,  145.3
2017-10-02 02:00:00, NULL   ,  174.9
2017-10-02 02:15:00, NULL   ,  155.3

但如果我做 FULL OUTER JOIN 得到：

从 TABLE_MACHINE_1 FULL OUTER JOIN TABLE_MACHINE_2 ON DATETIME

datetime,            sensor_1, datetime,            sensor_2
2017-10-01 21:00:00, 1.18047,  NULL,                NULL  
2017-10-01 21:15:00, 1.18023,  NULL,                NULL 
2017-10-01 21:30:00, 1.18059,  NULL,                NULL 
2017-10-01 21:45:00, 1.18067,  2017-10-01 21:45:00, 112.1
2017-10-01 22:00:00, 1.18056,  2017-10-01 22:00:00, 133.2
2017-10-01 22:15:00, 1.18147,  2017-10-01 22:15:00, 143.5
2017-10-01 22:30:00, 1.18104,  2017-10-01 22:30:00, 154.9
2017-10-01 22:45:00, 1.18111,  2017-10-01 22:45:00, 166.1
2017-10-01 23:00:00, 1.18103,  NULL,                NULL 
2017-10-01 23:15:00, 1.18072,  NULL,                NULL 
2017-10-01 23:30:00, 1.18083,  2017-10-01 23:30:00, 123.1
2017-10-01 23:45:00, 1.18079,  2017-10-01 23:45:00, 134.1
2017-10-02 00:00:00, 1.17996,  2017-10-02 00:00:00, 188.3
2017-10-02 00:15:00, 1.17987,  NULL,                NULL 
2017-10-02 00:30:00, 1.17968,  NULL,                NULL 
2017-10-02 00:45:00, 1.17893,  NULL,                NULL 
2017-10-02 01:00:00, 1.17852,  2017-10-02 01:00:00, 192.9
2017-10-02 01:15:00, 1.17833,  2017-10-02 01:15:00, 105.3
NULL,                NULL,     2017-10-02 01:30:00, 172.9 
NULL,                NULL,     2017-10-02 01:45:00, 145.3
NULL,                NULL,     2017-10-02 02:00:00, 174.9 
NULL,                NULL,     2017-10-02 02:15:00, 155.3

我需要在 postgresql 中的一个查询中解决它，我尝试使用完整的外连接子句失败。

我已经考虑过以下解决方案，但我无法实现它，因为当我传递变量“generate_series”时出现错误：

1. 创建一个变量来存储每个表的最大天数。

     datetime_end := to_char(now()::date,'YYY-MM-DD');
   

2. 将变量传递给以下查询：

     SELECT generate_series(
         timestamp without time zone '2017-10-01',
         timestamp without time zone datetime_end,
         '15 minute');
   

   3. 对两个表做LEFT JOIN之前的查询。

提前致谢。