A tip for lazy Oracle users – type less with ANSI DATE and TIMESTAMP SQL syntax

I am lazy, therefore if I can type less, I will type less.

Often people are surprised to see that Oracle supports the ANSI DATE and TIMESTAMP syntax in the SQL code, which allows me to shorten the lengthy TO_DATE( …. , ‘YYYY-MM-DD HH24:MI:SS’) syntax a bit. You can just type this if you want to compare some field to a date (day precision):

SQL> SELECT COUNT(*) FROM dba_objects WHERE created > DATE'2012-12-01';

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM dba_objects WHERE created > DATE'2012-01-01';

  COUNT(*)
----------
       804

This way you can always use the standard date format regardless of any NLS settings that may say otherwise.

Note that in above example, the date 2012-01-01 means 2012-01-01 00:00:00 really, so any objects created on that date from 00:00:01 onwards would be included in the results. If you want timestamp precision, then you can use the TIMESTAMP keyword:

SQL> SELECT COUNT(*) FROM dba_objects WHERE created > TIMESTAMP'2012-01-01 08:12:34';

  COUNT(*)
----------
       805

SQL> SELECT COUNT(*) FROM dba_objects WHERE created > TIMESTAMP'2012-01-01 08:12:34.000000000';

  COUNT(*)
----------
       805

SQL> SELECT COUNT(*) FROM dba_objects WHERE created > TIMESTAMP'2012-01-01 08:12:34.000000000 -08:00';

  COUNT(*)
----------
       805

I think this syntax works since Oracle 9i as this is where ANSI-SQL syntax was introduced into Oracle.

You can use this syntax also in some other situations, like the FLASHBACK TO TIMESTAMP and SELECT … AS OF TIMESTAMP clauses of a statement:

SQL> SELECT COUNT(*) FROM t AS OF TIMESTAMP TIMESTAMP'2012-12-29 20:00:10 +03:00';

  COUNT(*)
----------
     25237

SQL> SELECT COUNT(*) FROM t AS OF TIMESTAMP TIMESTAMP'2012-12-29 20:01:00 +03:00';

  COUNT(*)
----------
         1

SQL> ALTER TABLE t ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE t TO TIMESTAMP TIMESTAMP'2012-12-29 20:00:00 +03:00';

Flashback complete.

SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
     25237

Note the double “TIMESTAMP TIMESTAMP …” above. This is not a typo, the first TIMESTAMP tells you want to flash back by specifying a human timestamp as opposed to Oracle transactional timestamp in which case you would have used the “SCN” keyword. And the second TIMESTAMP is the ANSI timestamp specifier (you could use the good old TO_DATE() syntax there).

Enjoy saving time every time you type a SQL command with date&time logic in it (those of you who didn’t know about this feature, you can buy me beer when we meet).

Happy new year! :)

Update: Sayan Malakshinov has added an interesting piece of information into the comments section – that there’s also an undocumented TIME keyword available. Let’s do a quick test:


SQL> SELECT TIME'12:34:56.000000' a FROM dual;

A
---------------------------------------------------------------------------
12.34.56.000000000 PM

This returns only the time-of-day component apparently, but no day, month, year info.

Let’s try to create a table to see what datatype we’ll end up with then:

SQL> CREATE TABLE t AS SELECT TIME'12:34:56.000000' a FROM dual;
CREATE TABLE t AS SELECT TIME'12:34:56.000000' a FROM dual
*
ERROR at line 1:
ORA-00902: invalid datatype

Error!
Sayan mentioned an event 10407 in the comment, which allowed the table to be created. Let’s check the oraus.msg file and see what it’s about:

10407, 00000, "enable datetime TIME datatype creation"
// *Cause:
// *Action:  set this event to enable datetime datatype creation
// *Comment: This event is set when the user wants to create
//           a datetime datatype column.  The compatible=8.1.0.0.0 parameter
//           must also be set.

Let’s use this event:

SQL> ALTER SESSION SET EVENTS '10407 trace name context forever, level 1';

Session altered.

SQL> CREATE TABLE t AS SELECT TIME'12:34:56.000000' a FROM dual;

Table created.

Ok it’s possible now (but undocumented + unsupported too).

So after disabling the event and dropping the table I tried a few more variations with casting the TIME datatype to something else:

SQL> CREATE TABLE t AS SELECT CAST(TIME'12:34:56.000000' AS DATE) a FROM dual;
CREATE TABLE t AS SELECT CAST(TIME'12:34:56.000000' AS DATE) a FROM dual
                              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got TIME

Ok, can’t convert it to DATE. Let’s try TIMESTAMP:

SQL> CREATE TABLE t AS SELECT CAST(TIME'12:34:56.000000' AS TIMESTAMP) a FROM dual;

Table created.

SQL> @desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        TIMESTAMP(6)


SQL> SELECT * FROM t;

A
---------------------------------------------------------------------------
29-DEC-12 12.34.56.000000 PM

Converting to TIMESTAMP worked – looks like the current DATE is chosen as the date component for the result.

Hmm, I wonder if we can somehow concatenate a date component and a time component together?


SQL> SELECT TRUNC(sysdate - 100) + TIME'15:00:00' FROM dual;
SELECT TRUNC(sysdate - 100) + TIME'15:00:00' FROM dual
                            *
ERROR at line 1:
ORA-30087: Adding two datetime values is not allowed

Well, apparently not.

Anyway, my flight departs in 40 minutes, so got to get moving. Thanks Sayan for the TIME addition – it’s good to learn new stuff (every day ;-)

This entry was posted in Oracle, Productivity. Bookmark the permalink.

31 Responses to A tip for lazy Oracle users – type less with ANSI DATE and TIMESTAMP SQL syntax

  1. Plus undocumented time :)
    SQL>> select time’23:59:59′ from dual

    TIME’23:59:59′
    —————————————–
    11.59.59.000000000 PM

    SQL>> alter session set events ’10407 trace name context forever, level 1′;
    Session altered.
    SQL>> create table t_time(tm time);
    Table created.

    Happy new year!

    • Tanel Poder says:

      Nice one, Sayan! I didn’t know that. I will update the blog entry with this addition.

      • Is this really a different datatype?? Which ID does it have? How is it stored? I’m at home right now and too lazy to fire up the VPN + RDP connection to get at an Oracle database… and I’d have to research how to get the ID and about the (I think it’s called) dump() function.

      • Thanks, Tanel!
        One another addition: 10407 also enables “to_time” function:

        SQL> select to_time(’01-02-03′) from dual;
        select to_time(’01-02-03′) from dual
        *
        ERROR at line 1:
        ORA-00904: “TO_TIME”: invalid identifier

        SQL> ALTER SESSION SET EVENTS ’10407 trace name context forever, level 1′;
        Session altered.

        SQL> select to_time(’01-02-03′) from dual;
        TO_TIME(’01-02-03′)
        —————————————————————————
        01.02.03.000000000 AM

        1 row selected.

  2. The first mention of datetime literals in the doc was in 10gR2.

    It used to be an undocumented feature, back in Oracle 8 or even older.

    e.g. in 8i on asktom

  3. Connor says:

    When you create a table partitioned by timestamp, ‘high_value’ seems to utilise this syntax as well

  4. The date ‘yyyy-mm-dd’ can be particularly useful for distributed queries.
    Some years ago I found that a filter of the form “date_col = to_date()” on a remote table wouldn’t be applied remotely, instead rows would be returned to the local machine before the date predicate was applied. Changing the code to the date ‘dddd-mm-dd’ form would result in the date being sent to the remote database as a bind variable and the predicate being applied there – reducing the network traffic considerably in my case.

    I’ll have to see if I can find the example in my library – it might be an old limitation that has since been fixed.

    • Tanel Poder says:

      Goot stuff, interesting :)

    • I’ve found the script – and I was wrong (though still interesting ;)

      (In some versions of Oracle) A predicate using date ‘yyyy-mm-dd’ arrives at the remote as a fully qualified call to to_date(‘yyyy-mm-dd’, {forma}) with the literal preserved. The thing that magically changes into a bind variable en route is cast(‘dd-mm-yyyy’ as date), and the date literal has to be in a format that your session will be able to read unambiguously – I even have a reference that suggests it has to be in the nls_date_format.

      I’m obviously going to have to find time to re-run, refine and re-document the script to cover a few more variations – at present the notes only go from 8.1.7.4 through 9.2.0.8 to 10.2.0.1 and they’re a little messy.

  5. Uwe Hesse says:

    It was only recently that I got aware of this “ANSI DATE and TIMESTAMP SQL syntax”, I must confess. Thanks to a Blog comment :-) One UFO (Unknown Feature of Oracle) less for me.

  6. zeya says:

    Hi sir,
    suppose i have creation date with time like on 15-06-2004 i have many “created” column with differ with time
    when i m fire this query…
    select object_name,created from dba_objects where created =date’2004-06-15′
    it doesn’t provide any output
    suggest me……

  7. Matthias Rogel says:

    interestingly, creating a table with undocumented datatype TIME results in “ORA-00902: invalid datatype”, except when we have event 10407 set.

    However, we can create a view with this undocumented datatype with no difficulty:

    sokrates@11.2 > CREATE TABLE t AS SELECT TIME’12:34:56.000000′ a FROM dual;
    CREATE TABLE t AS SELECT TIME’12:34:56.000000′ a FROM dual
    *
    ERROR at line 1:
    ORA-00902: invalid datatype

    sokrates@11.2 > CREATE VIEW v AS SELECT TIME’12:34:56.000000′ a FROM dual;

    View created.

    sokrates@11.2 > desc v
    Name Null? Type
    —————————————– ——– —————————-
    A TIME(9)

    sokrates@11.2 >

  8. Gary says:

    You can create a view using the TIME data type without any special settings
    create view gmd as select time ’13:34:00′ from dual;
    DESCRIBE shows it as a TIME(9)
    DUMP indicates that it has the same length and structure as TIMESTAMP but the bytes associated with the date component are all zero. The numeric data type is 185 for TIME but 187 for TIMESTAMP.

  9. Just a slight heads-up on using the TIMESTAMP literal when comparing to DATE columns:

    SQL> create table test_objects
    2 as
    3 select * from dba_objects
    4 /

    Table created.

    SQL> create index test_objects_created on test_objects (
    2 created
    3 )
    4 /

    Index created.

    SQL> begin
    2 dbms_stats.gather_table_stats(USER,’TEST_OBJECTS’);
    3 end;
    4 /

    PL/SQL procedure successfully completed.

    Using DATE literal is a DATE datatype, so it will index range scan:

    SQL> select count(*)
    2 from test_objects
    3 where created > DATE ’2012-12-01′
    4 /

    COUNT(*)
    ———-
    2118

    ——————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 8 | 7 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 8 | | |
    |* 2 | INDEX RANGE SCAN| TEST_OBJECTS_CREATED | 1922 | 15376 | 7 (0)| 00:00:01 |
    ——————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————
    2 – access(“CREATED”>TO_DATE(‘ 2012-12-01 00:00:00′, ‘syyyy-mm-dd hh24:mi:ss’))

    But DATE literal does not support a time of day so we use TIMESTAMP literal, which is a TIMESTAMP datatype, so now it full table scans and converts our DATE column to a TIMESTAMP for comparison:

    SQL> select count(*)
    2 from test_objects
    3 where created > TIMESTAMP ’2012-12-01 18:00:00′
    4 /

    COUNT(*)
    ———-
    2118

    ———————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ———————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 8 | 569 (5)| 00:00:02 |
    | 1 | SORT AGGREGATE | | 1 | 8 | | |
    |* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 6757 | 54056 | 569 (5)| 00:00:02 |
    ———————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————
    2 – filter(INTERNAL_FUNCTION(“CREATED”)>TIMESTAMP’ 2012-12-01
    18:00:00,000000000′)

    When using TO_DATE instead, we get our index range scan back:

    SQL> select count(*)
    2 from test_objects
    3 where created > to_date(’2012-12-01 18:00:00′,’YYYY-MM-DD HH24:MI:SS’)
    4 /

    COUNT(*)
    ———-
    2118

    ——————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 8 | 7 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 8 | | |
    |* 2 | INDEX RANGE SCAN| TEST_OBJECTS_CREATED | 1915 | 15320 | 7 (0)| 00:00:01 |
    ——————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————
    2 – access(“CREATED”>TO_DATE(‘ 2012-12-01 18:00:00′, ‘syyyy-mm-dd hh24:mi:ss’))

    Or we can cast the TIMESTAMP as a DATE with same results (but then we haven’t really saved much typing :-) :

    SQL> select count(*)
    2 from test_objects
    3 where created > cast(TIMESTAMP ’2012-12-01 18:00:00′ as date)
    4 /

    COUNT(*)
    ———-
    2118

    ——————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 8 | 5 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 8 | | |
    |* 2 | INDEX RANGE SCAN| TEST_OBJECTS_CREATED | 6757 | 54056 | 5 (0)| 00:00:01 |
    ——————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————
    2 – access(“CREATED”>CAST(TIMESTAMP’ 2012-12-01 18:00:00,000000000′ AS date))

    Or yet another alternative is to add an INTERVAL to the DATE literal, which Oracle recognizes as being a DATE literal expression:

    SQL> select count(*)
    2 from test_objects
    3 where created > DATE ’2012-12-01′ + INTERVAL ’18:00:00′ HOUR TO SECOND
    4 /

    COUNT(*)
    ———-
    2118

    ——————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 8 | 7 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 8 | | |
    |* 2 | INDEX RANGE SCAN| TEST_OBJECTS_CREATED | 1915 | 15320 | 7 (0)| 00:00:01 |
    ——————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————
    2 – access(“CREATED”>TO_DATE(‘ 2012-12-01 18:00:00′, ‘syyyy-mm-dd hh24:mi:ss’))

    When I first learned of the DATE and TIMESTAMP literals, I tended to use TIMESTAMP rather than TO_DATE often when I had a literal date value including a time part. But then I discovered that I often got full table scans where I had expected index range scans and found out the reason is the implicit conversion taking place when comparing a DATE and a TIMESTAMP ;-)

    • Or you could create a function-based index on the to_date() on the timestamp column…

      • The point of my heads-up was cases of a DATE column with an existing index, where you might be tempted to use a timestamp LITERAL and be surprised at the effect.
        Anyway, it would also be a bit overkill in my opinion to create a function-based index simply to allow me to type less by using a timestamp literal rather than for example to_date :-)

        • Tanel Poder says:

          Yep, there’s also a catch that you may already have many composite indexes which include the timestamp column as it is. Now if you create a function based index, would you create a function based alternative index for each of the indexes … writing a bit more code is better long term strategy here, although FBIs can often offer a quick workaround to some problem (especially if code “can’t be changed”)

    • Kim,

      thanks for this demonstration.

      As I mentioned on Uwe’s Blog,
      http://uhesse.com/2012/12/13/partition-pruning-do-dont/#comment-5309
      , a similar effect can occurs when you have a range-partitioned-table partitioned by a DATE-column ( call it DATEKEY )
      and you add query it like
      … where DATEKEY between and ( and DATE1 and DATE2 are not Bind-Variables):

      partition-pruning will not occur, when you use a TIMESTAMP-literal.
      You either have to use a
      a. TO_DATE or a
      b. DATE-literal or a
      c. CAST(TIMESTAMP-literal as DATE)
      d. DATE-literal+ number or a
      e. DATE-literal + INTERVAL
      , so that partition-pruning has the chance to get into play

      “When I first learned of the DATE and TIMESTAMP literals, I tended to use TIMESTAMP rather than TO_DATE often when I had a literal date value including a time part. ”
      So did I.

      My favourite way now is to use
      DATE-literal + INTERVAL

      like in
      http://uhesse.com/2012/12/13/partition-pruning-do-dont/#comment-5383

      Cheers
      Matthias

    • Kim, I usually use implicit conversion:

      timestamp'2013-01-01' + 0>created
      
      • It is a short and easy way, sure.
        I just tend to dislike such a solution somewhat, as it is “obscure” and a new developer on the project could easily think “what is this adding of zero – that must a mistake, so I will delete the + 0″ – unless of course you add a comment like /* This + 0 is to force implicit conversion to DATE */ in which case you haven’t saved any typing anyway :-)
        If I use a clearly understandable method like to_date with an explicit format or date + interval literals, then I may have used a few extra keystrokes in the code, but it becomes self-explanatory so I need fewer comments and thus less typing.

        • Tanel Poder says:

          Btw, I tend to use the “long” TO_DATE() / TO_TIMESTAMP() approach myself when writing application code, but when writing just some quick DBA commands or scripts, then tend to use the lazy approach more …

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>