Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Question Unanswered: Simple Time Problem

    Now that the clocks have just gone forward an hour, all of my times are wrong. How do I get the correct time using the TO_CHAR function for a time with the an offset from GMT?

    Thanks

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

    Re: Simple Time Problem

    Originally posted by BeyondCloister
    Now that the clocks have just gone forward an hour, all of my times are wrong. How do I get the correct time using the TO_CHAR function for a time with the an offset from GMT?

    Thanks
    The new Oracle datatype TIMESTAMP WITH [LOCAL] TIME ZONE caters for this, I guess (haven't tried it). With normal DATEs you will just have to make the adjustment to the DATE value BEFORE you TO_CHAR it. e.g.:

    SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI') bst_time,
    2 TO_CHAR(SYSDATE-1/24,'HH24:MI') gmt_time
    3 FROM DUAL;

    BST_T GMT_T
    ----- -----
    13:30 12:30

  3. #3
    Join Date
    Feb 2003
    Posts
    4
    How would this work for retrieving times from my database when there are times entered prior to the start of BST and new times being returned in the same query?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by BeyondCloister
    How would this work for retrieving times from my database when there are times entered prior to the start of BST and new times being returned in the same query?
    I suppose you could do this:

    SELECT
    CASE
    WHEN datecol < TO_DATE('30-MAR-2003 02:00','DD-MON-YYYY HH24:MI')
    THEN datecol
    ELSE datecol-1/24
    END true_datetime
    FROM ...

    (You could wrap the CASE expression in a function to make it more user-friendly).

    But that only works back until whenever BST last ended in October 2002, unless you add a lot more cases!

    It really sounds like you need to be using TIMESTAMP WITH LOCAL TIMEZONE, which takes care of this sort of thing for you (I believe). DATE just isn't that clever.

Posting Permissions

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