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</pre>

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</pre>

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</pre>

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 ;-)