Oracle Date Datatype Should be DateTime DataType

thatjeffsmith Database Stuff, SQL Developer 12 Comments

Tell Others About This Story:

Want to confuse an Oracle database n00b? Ask them if their query takes into account the time component of a date when they code their WHERE blahDate between … AND … logic.

But sir, my dates do not have a time associated with it. You must be confusing this with TimeStamp. See, that has the word ‘time’ in it, so that is where Oracle keeps time stuff.

It’s at this point that I seriously start to doubt the reliability of any of the code this person has written.

How does this happen?

The datatype is called ‘Date’

What’s today’s date? Oh today is February 10th, 2012 & 9:39:25 in the AM, Eastern Standard Time. Dude, I didn’t ask for the time, I asked for the date!

Let’s check out line numero uno of the Oracle Docs description of the DATE datatype:
“The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).”

Well that’s pretty straightforward. Surely when n00b starts their career in IT, they read all the manuals first? I mean, it’s not like they’re this guy?

We need to make sure Robert Culp is there to catch the n00b when he tries to fly and can’t figure out where or how to land!

When they query it, they only see the date

The n00b’s misconception is further strengthened when they start querying fields that are stored as DATEs. Let’s take a quick look at this phenomena in SQL Developer:

Look ma, no time! Oh wait a second...

So why the ‘no time’ in SQL Developer? We need defaults, and showing a list of dates that include a time component of 00:00:00 takes a lot of real estate in the data grid. So we compromised. And it’s probably OK MOST of the time. But –

Make it your job to coach the new folks

When ‘new guy’ or ‘new gal’ starts on the job, offer to do code reviews. If they resist, make it an offer they can’t refuse. Learn how to give truly CONSTRUCTIVE criticism. Be prepared with examples and stories of how YOU cost the company $27,123 back in the day because you were neglecting sales that occurred after midnight.

Even better, offer to host a lunch and learn! Buy a pizza and a 12-pack of soda (never beer!) and give a quick 20 minute overview the topic at hand.

How to change the default DATE display behavior in SQL Developer

Open the preferences, and search for ‘NLS.’

The defaults that are showing on my install are based off of a my local OS. I’m in America, so it’s showing as such. If I were in China, we’d be seeing Chinese settings most likely.

Simply add the time component to the NLS_DATE_FORMAT setting in SQL Developer and you’ll see the time component whenever you query a DATE field…even if it’s 00:00:01!

OR

If you want to have SQL Developer just show the data as the database intends based on the NLS_DATABASE_PARAMETERS, then tell SQL Developer to ‘Skip NLS Settings.’

Configuring SQL Developer to display Time Component of a DATE

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 12

  1. If this works
    SELECT * FROM PS_ACA_PER_ELIG

    why does
    DESCRIBE PS_ACA_PER_ELIG

    fails with
    DESCRIBE PS_ACA_PER_ELIG
    ERROR:
    ——————————————–
    ERROR: object PS_ACA_PER_ELIG does not exist

    Is it permission/security related?

    1. thatjeffsmith Post
      Author
      1. The table is a PeopleSoft delivered table and local object.

        SQL Developer ver 3.2.20.10 Build Main 10.20

        Another thing I noticed is that I am unable to open/expand the folder to display all the tables which kinda leads me to think it may be a configuration related issue.

    1. thatjeffsmith Post
      Author
  2. JeffS Post
    Author

    >> when relying on Oracle for implicit type conversions
    Never! Don’t leave it up to chance I say. Of course you said the code is delivered, so I understand it’s out of your hands.

    Have you tried using the extract() function to pull out the different elements of an interval and then feeding that output to your analytic and aggregate functions? I have to admit I haven’t tried what you’re talking about, but I’d be happy to investigate a better solution for you if you could give me an example.

    Thanks for the backup Dave! I was getting killed on Twitter this morning 🙂

  3. Can’t agree with you more on the Date data type issue. I always make it a habit of changing my NLS_DATE_FORMAT to include 4 digit years and the time component. Of course this can cause problems when working with delivered code that fails to provide proper date masks when using the to_char() and to_date() functions or when relying on Oracle for implicit type conversions which no longer match the expected value.

    As for INTERVALS they are one of my favorite data types. Unfortunately they are not terribly well supported by oracle. The to_char() function ignores masks when used with an interval, there are no corresponding to_[ym|ds]interval() functions to convert strings to intervals. There are the numto[ym|ds]interval() functions. My biggest gripe though is that analytic and aggregate functions don’t work with intervals.

  4. Personal pet peeve of mine with Oracle is that the DATE data type is more akin to TIMESTAMP. However, that being said, the ability to manipulate date values, ahem, timestamp values in Oracle with simple arithmetic instead of INTERVALS is very nice.

    Good tip on changing the NLS settings in the tool to reflect what is really being returned. Thanks!

    1. JeffS Post
      Author

      Well, you had me until you mentioned TIMESTAMP. TIMESTAMP allows you to define a time down to the fraction of a second. Which is very important if you start to look at things like stock trades. I would use Date whenever it’s OK to just know the date and time of day down to the second.

      Funny that you mention INTERVALs. I just recently took the time to figure out how those exactly worked because for some reason they had always seemed foreign to me.

Leave a Reply

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