| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-21-03, 10:03
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
|
timestamp problem with no date
|
|
Let's assume it is today (July 21) and the time is 00:05 (12:05 AM).
I receive a file with data I need to load. It comes in 5 minute increments and has a timestamp with no date (this is why I hate this file).
The data will lok like this:
Code:
"D","4006","23:55:02",51.75,0,-1.42,50.33
"D","4007","23:55:02",51.75,0,0.62,52.37
"D","4008","23:55:02",51.75,0,-0.76,50.99
"D","4000","00:00:03",53.75,0,0.23,53.98
"D","4001","00:00:03",53.75,0,-3.46,50.29
"D","4002","00:00:03",53.75,0,0.37,54.12
"D","4008","00:00:03",53.75,0,-0.8,52.95
"D","4000","00:05:05",53.39,0,0.2,53.59
"D","4001","00:05:05",53.39,0,-3.25,50.14
"D","4002","00:05:05",53.39,0,0.37,53.76
My problem is this:
Since there is no date, I have to assume it is always 'Today'. Unfortunately, this means that the 11:55 PM data gets loaded as the FUTURE (July 21 11:55 PM) instead of 11:55 PM yesterday (July 20).
I directly load this into the table with SQL*Loader.
The only solutions I have so far are:
1. After the data is loaded, take the dates in the future and load them as date-1
2. Load data into a temp table, and write some crazy manipulation script for any dates in the future, and then load the data into the actual table.
Can anyone think of anything else as a solution?
Thanks for your time (no punn intended).
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-21-03, 10:32
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
|
Re: timestamp problem with no date
Quote:
Originally posted by The_Duck
Let's assume it is today (July 21) and the time is 00:05 (12:05 AM).
I receive a file with data I need to load. It comes in 5 minute increments and has a timestamp with no date (this is why I hate this file).
The data will lok like this:
Code:
"D","4006","23:55:02",51.75,0,-1.42,50.33
"D","4007","23:55:02",51.75,0,0.62,52.37
"D","4008","23:55:02",51.75,0,-0.76,50.99
"D","4000","00:00:03",53.75,0,0.23,53.98
"D","4001","00:00:03",53.75,0,-3.46,50.29
"D","4002","00:00:03",53.75,0,0.37,54.12
"D","4008","00:00:03",53.75,0,-0.8,52.95
"D","4000","00:05:05",53.39,0,0.2,53.59
"D","4001","00:05:05",53.39,0,-3.25,50.14
"D","4002","00:05:05",53.39,0,0.37,53.76
My problem is this:
Since there is no date, I have to assume it is always 'Today'. Unfortunately, this means that the 11:55 PM data gets loaded as the FUTURE (July 21 11:55 PM) instead of 11:55 PM yesterday (July 20).
I directly load this into the table with SQL*Loader.
The only solutions I have so far are:
1. After the data is loaded, take the dates in the future and load them as date-1
2. Load data into a temp table, and write some crazy manipulation script for any dates in the future, and then load the data into the actual table.
Can anyone think of anything else as a solution?
Thanks for your time (no punn intended).
|
You could manipulate the value in the control file. For example, this will make all times after 12:00 be yesterday, before 12:00 today:
...,
ts "decode(sign(to_number(substr(:ts,1,2))-12),1,to_date(to_char(sysdate-1,'DDMMYYYY')||:ts, 'DDMMYYYYHH24:MI  S'),to_date(to_char(sysdate,'DDM MYYYY')||:ts, 'DDMMYYYYHH24:MI S'))",
...
|
|
07-21-03, 10:52
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
thank you
|
|
My tests with your supplied code work.
God bless Tony Andrews!
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-21-03, 15:51
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
strange, but it does not work with this data.
Keeps loading it as yesterday
Code:
"D","4008","14:55:02",84.35,-31.45,-0.61,52.29
I am not familiar with the SIGN function so it is difficult for me to debug.
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-21-03, 16:44
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
Quote:
Originally posted by The_Duck
strange, but it does not work with this data.
Keeps loading it as yesterday
Code:
"D","4008","14:55:02",84.35,-31.45,-0.61,52.29
I am not familiar with the SIGN function so it is difficult for me to debug.
|
Can't see any reason for that - unless there is a space between the " and 14 like " 14:55:02" ?
The SIGN function returns 1 if the argument is > 0, -1 if < 0 and 0 if it is 0. So this:
sign(to_number(substr(:ts,1,2))-12)
takes the first 2 chars of :ts, i.e. '14' here, converts to a number 14, deducts 12 to get 2. sign(2) = 1, so the first DECODE result is used, which uses yesterday's date. If the first 2 digits are 12 or less, the SIGN returns 0 or -1, so the seconds DECODE result is used (i.e. today's date).
|
|
07-21-03, 17:00
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
I see.
I ended up converting it slightly to this:
PHP Code:
MKT_PRICE_DT "DECODE(SIGN(To_Date(To_Char(sysdate,'DD')||:MKT_PRICE_DT,'DDHH24:MI:SS')-sysdate),-1,
to_date(to_char(sysdate,'DDMMYY')||substr(:MKT_PRICE_DT,1,5),'DDMMYYHH24:MI')+(4/24),
to_date(to_char(sysdate-1,'DDMMYY')||substr(:MKT_PRICE_DT,1,5),'DDMMYYHH24:MI')+(4/24))",
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
timestamp problem with no date
