Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: 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 ...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: timestamp problem with no date

    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:MIS'),to_date(to_char(sysdate,'DDMMYYYY')||:ts, 'DDMMYYYYHH24:MIS'))",
    ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs up 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 ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    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 ...

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    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 ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •