oracle convert timestamps to other time masks
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10904081/
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
oracle convert timestamps to other time masks
提问by cjd143SD
How do I convert these number columns (timestamp, event_dt) to a date or time mask? I am trying this:
How do I convert these number columns (timestamp, event_dt) to a date or time mask? I am trying this:
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
回答by Ben
Firstly, neverstore date or timestamp data-types in anything but a date or timestamp column. It causes no end of pain; as you're gathering.
Firstly, neverstore date or timestamp data-types in anything but a date or timestamp column. It causes no end of pain; as you're gathering.
As your "timestamps" have 11 orders of magnitude I'm going to guess that you inserted them in the form yyyymmddhh24mi, and that they're not seconds since an unspecified epoch or anything like that.
As your "timestamps" have 11 orders of magnitude I'm going to guess that you inserted them in the form yyyymmddhh24mi, and that they're not seconds since an unspecified epoch or anything like that.
If you want to convert this then you have to firstly convert them into a character, then into a date. You don't actually need a timestamp as these differ from dates only in fractional seconds.
If you want to convert this then you have to firstly convert them into a character, then into a date. You don't actually need a timestamp as these differ from dates only in fractional seconds.
It'd look something like the following:
It'd look something like the following:
select to_date(to_char(timestamp),'yyyymmddhh24mi') as my_timestamp
from top_read_events
On a side note nevercall a column timestamp, date, group or another reserved word. It causes too many problems. Personally I normally go for "tstamp" but that's just personal preference.
On a side note nevercall a column timestamp, date, group or another reserved word. It causes too many problems. Personally I normally go for "tstamp" but that's just personal preference.
If you want to convert your "timestamp" into a character you'd then have to convert it into a character again.
If you want to convert your "timestamp" into a character you'd then have to convert it into a character again.
select to_char(to_date(to_char(timestamp)
,'yyyymmddhh24mi')
,'yyyy-mon-dd hh24:mi:ss') as my_timestamp
from top_read_events
The seconds will always be 00as you don't have these. Note that I only use explicitnumber to character to date to character conversion. It makes it more obvious to the coders who come after you what you're doing, including yourself 2 years down the line, and there's no chance of the interpreter making a mistake over intention and comparing a number to a character etc.
The seconds will always be 00as you don't have these. Note that I only use explicitnumber to character to date to character conversion. It makes it more obvious to the coders who come after you what you're doing, including yourself 2 years down the line, and there's no chance of the interpreter making a mistake over intention and comparing a number to a character etc.
For this reason I'd definitely change where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'. As you want to date comparison change this into a date and to it that way, i.e.
For this reason I'd definitely change where timestamp= to_char(sysdate-2,'yyyymmdd') || '0000'. As you want to date comparison change this into a date and to it that way, i.e.
where trunc(to_date(to_char(timestamp),'yyyymmddhh24mi'),'dd') = trunc(sysdate -2)
trunc()truncates this at the day level in this instance.
trunc()truncates this at the day level in this instance.
回答by Justin Cave
First off, storing dates and timestamps in columns that are not DATEor TIMESTAMPis poor data modeling. Life would be much easier if you stored the data in columns with the proper data types.
First off, storing dates and timestamps in columns that are not DATEor TIMESTAMPis poor data modeling. Life would be much easier if you stored the data in columns with the proper data types.
Second, what format are your numeric columns using to store date and/or timestamp data? You don't post an example of an actual value-- all we know is that they are 12 digits and begin with 2011. I'll guess that they are stored in the format YYYYMMDDHH24MI with no seconds-- you'll need to tell us if that guess happens to be incorrect.
Second, what format are your numeric columns using to store date and/or timestamp data? You don't post an example of an actual value-- all we know is that they are 12 digits and begin with 2011. I'll guess that they are stored in the format YYYYMMDDHH24MI with no seconds-- you'll need to tell us if that guess happens to be incorrect.
Assuming that guess is correct, you would need to convert the number to a string, the string to a date, and then the date back to another string in a different format. Obviously, this is a bit awkward (and one of the many reasons to store things in the proper data types). Something like
Assuming that guess is correct, you would need to convert the number to a string, the string to a date, and then the date back to another string in a different format. Obviously, this is a bit awkward (and one of the many reasons to store things in the proper data types). Something like
to_char( to_date( to_char( timestamp ),
'YYYYMMDDHH24MI' ),
'YYYY-MON-DD HH24:MI:SS' )
If your numeric columns are stored differently or there are any cases where data is not stored in the correct format, life gets even less pleasant.
If your numeric columns are stored differently or there are any cases where data is not stored in the correct format, life gets even less pleasant.
