Oracle——基于varchar时间戳提取月度数据

Question

我有一个oracle数据库，我正试图从每月提取数据。

我正在查询的表和字段具有以下结构

select ID, lastmodifieddate as LMDate from salesforce.X_TASK WHERE ROWNUM <= 10
('ID', <cx_Oracle.DbType DB_TYPE_VARCHAR>, 18, 18, None, None, 1)
('LMDATE', <cx_Oracle.DbType DB_TYPE_VARCHAR>, 25, 25, None, None, 1)
('00T4N00002QhFvjUAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFvmUAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFvnUAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFvsUAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFvtUAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFw0UAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFw1UAF', '2019-09-06T21:05:04.000Z')
('00T4N00002QhFwAUAV', '2019-09-06T21:05:09.000Z')
('00T4N00002QhFwBUAV', '2019-09-06T21:05:09.000Z')
('00T4N00002QhFwMUAV', '2019-09-06T21:05:09.000Z')

从varchar时间戳字段中提取日期的最佳方法是什么?

我已经尝试更改字段to_char

select ID, to_char(LASTMODIFIEDDATE, 'YYYY-MM-DD') from salesforce.X_TASK 
WHERE LASTMODIFIEDDATE >= to_date('2020-05-31', 'YYYY-MM-DD') 
and  LASTMODIFIEDDATE < to_date('2020-06-30', 'YYYY-MM-DD')

但是最终会出现这个错误

ORA-01861: literal does not match format string

如果我尝试将其更改为日期也一样

select ID, to_date(LASTMODIFIEDDATE, 'YYYY-MM-DD') from salesforce.X_TASK 
WHERE LASTMODIFIEDDATE >= to_date('2020-05-31', 'YYYY-MM-DD') 
and  LASTMODIFIEDDATE < to_date('2020-06-30', 'YYYY-MM-DD')
ORA-01861: literal does not match format string

Answer 1

时间戳到底是什么?它看起来怎么样?日期，时间到…秒?

无论如何:当你将时间戳存储为字符串时(一般来说，这是个坏主意)，如果它的所有值都具有相同的格式(例如，25.02.2021 20:02:00是dd.mm.yyyy hh24:mi:ss)，那么最简单的方法就是使用SUBSTR函数。在你的例子中:

select id,
substr(lastmodifieddate, 1, 10) as datum
from salesforce.X_TASK 
where to_date(lastmodifieddate, 'dd.mm.yyyy') >= to_date('2020-05-31', 'YYYY-MM-DD') 
and to_date(lastmodifieddate, 'dd.mm.yyyy') <  to_date('2020-06-30', 'YYYY-MM-DD');

当然，如果格式不是dd.mm.yyyy(如在我的示例中)，则使用您的格式。请注意，如果存在不遵循这种格式的值，包含字母或诸如此类的值，查询将失败。这就是为什么我说将时间戳(或日期)存储为字符串是一个非常非常糟糕的主意。

---

看了你的评论后:似乎列包含了大量的数据，而不仅仅是时间戳。那好，把日期的那部分提取出来。SUBSTR + INSTR会做:

SQL> with test (col) as
2    (select q'[<cx_Oracle.DbType DB_TYPE_VARCHAR>, 25, 25, None, None, 1) ('2016-10-27T19:27:14.000Z')]'
3     from dual
4    )
5  select substr(col, instr(col, '(') + 2, 10) datum
6  from test
7  /
DATUM
----------
2016-10-27
SQL>

现在，将TO_DATE与适当的格式掩码应用于WHERE子句:

select id,
to_date(substr(lastmodifieddate, instr(lastmodifieddate, '(') + 2, 10), 'yyyy-mm-dd') datum
from x_task
where to_date(substr(lastmodifieddate, instr(lastmodifieddate, '(') + 2, 10), 'yyyy-mm-dd') >= to_date('2020-05-31', 'YYYY-MM-DD') 
and to_date(substr(lastmodifieddate, instr(lastmodifieddate, '(') + 2, 10), 'yyyy-mm-dd') <  to_date('2020-06-30', 'YYYY-MM-DD');