oracle将时间戳转换为其他时间掩码

Question

如何将这些数字列(timestamp、event_dt)转换为日期或时间掩码？我正在尝试这样做：

    select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS'), domain_c, to_char(event_date,'YYYY-MON-DD HH24:MI:SS'), total_reads from TOP_READ_EVENTS where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'
                         *
ERROR at line 1:
ORA-01481: invalid number format model


SQL> desc top_read_events;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 YEAR                           NUMBER
 QUARTER                        NUMBER
 MONTH                          NUMBER
 DAY                            NUMBER
 HOUR                           NUMBER
 TIMESTAMP                      NUMBER
 DOMAIN_C                       VARCHAR2(255)
 EVENT_DT                       NUMBER
 TOTAL_READS                        NUMBER




select timestamp, domain_c, event_dt, total_reads from TOP_READ_EVENTS where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'    

 TIMESTAMP DOMAIN_C              EVENT_DT  TOTAL_READS
---------- ------------------------------  ------------ -------------
2.0111E+11 b.e.att-mail.com         2.0111E+11         14406
2.0111E+11 bounce.emailinfo2.bestbuy.com    2.0111E+11         14156
2.0111E+11 bounce.bluestatedigital.com      2.0111E+11         13701
2.0111E+11 plentyoffish.com         2.0111E+11         13384
2.0111E+11 mail.classmates.com          2.0111E+11         13281
2.0111E+11 comcast.net              2.0111E+11         13241
2.0111E+11 uniquelistsmail.com          2.0111E+11         13135
2.0111E+11 tankgorilla.com          2.0111E+11         12835
2.0111E+11 frigidphoenix.com            2.0111E+11         12657

Answer 1

首先，不要将date或timestamp数据类型存储在date或timestamp列。它会带来永无止境的痛苦；当你聚集的时候。

由于您的“时间戳”有11个数量级，我将猜测您是以yyyymmddhh24mi的形式插入它们的，并且它们不是从未指定的时期或类似的时间开始的秒数。

如果你想转换它，你必须先把它们转换成一个字符，然后再转换成一个日期。您实际上不需要时间戳，因为这些时间戳与日期仅以小数秒为单位不同。

它看起来像下面这样：

select to_date(to_char(timestamp),'yyyymmddhh24mi') as my_timestamp
  from top_read_events

另外，永远不要调用列的时间戳、日期、组或其他保留字。它造成了太多的问题。就我个人而言，我通常会选择"tstamp“，但这只是我个人的偏好。

如果你想把你的“时间戳”转换成一个字符，那么你必须再次把它转换成一个字符。

select to_char(to_date(to_char(timestamp)
                       ,'yyyymmddhh24mi')
              ,'yyyy-mon-dd hh24:mi:ss') as my_timestamp
  from top_read_events

秒数将始终是00，因为您没有这些。请注意，我只使用显式的数字到字符到日期到字符的转换。这会让你后面的程序员更清楚你在做什么，包括两年后的你自己，而且不会有解释器在意图上犯错误，并将数字与字符进行比较等。

出于这个原因，我肯定会更改where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'。当您想要进行日期比较时，请将其更改为日期，并以这种方式更改为日期。

where trunc(to_date(to_char(timestamp),'yyyymmddhh24mi'),'dd') = trunc(sysdate -2)

在此实例中，trunc()在日期级别截断此参数。

Answer 2

首先，在非DATE或TIMESTAMP的列中存储日期和时间戳不是很好的数据建模。如果您将数据存储在具有适当数据类型的列中，生活将会容易得多。

其次，您的数值列使用什么格式来存储日期和/或时间戳数据？你不会张贴一个实际值的例子--我们只知道它们是12位数，从2011开始。我猜它们是以YYYYMMDDHH24MI格式存储的，没有秒数--如果这个猜测是不正确的，您需要告诉我们。

假设猜测是正确的，您将需要将数字转换为字符串，将字符串转换为日期，然后将日期转换回另一个不同格式的字符串。显然，这有点笨拙(这也是以适当的数据类型存储数据的众多原因之一)。就像这样

to_char( to_date( to_char( timestamp ), 
                  'YYYYMMDDHH24MI' ),
         'YYYY-MON-DD HH24:MI:SS' )

如果您的数值列以不同的方式存储，或者有任何情况下数据没有以正确的格式存储，生活就会变得更加不愉快。