All databases stand on a tripod of datatypes: strings, numbers and dates. And though they might dress them in fancy clothing – varchar2, clob, float, integer – strings are really just strings, and numbers are really just numbers. But dates — dates are interesting.

In this article I’ll talk to you about dates, about time, and about how both are captured and calculated in an Oracle database. 

The DATE Datatype

DATE is the main – or rather, original – datatype used in Oracle for holding dates. Beneath the plainness of its name, it hides a little depth.  Firstly, for example, it doesn’t really hold a date, instead it records a datetime. It’s a seven byte store of century, year, month, day and hour, minute and second. 

Oracle does not compel you to supply a time element each time you enter a date, but it’s probably worth bearing in mind that one is always recorded (the default time is midnight). Let me show you what I mean – and in the process we can chat about the to_date function.

Let’s start by creating a table with a date column.

Now that’s done, let me talk to you about a dilemma that dates present databases with. When you present Oracle with, for example, ’21 January 2017′, how is it supposed to recognise that this is a date and not just a character string? Likewise, how’s it to know that ’21/01/2017′ isn’t just some complex piece of arithmetic?

It resolves this conundrum with the to_date function. The to_date function accepts a character string that represents the date along with another one that tells it how to interpret that date. Here’s what I mean:

It’s as simple as that (by the way, here’s a short list of the codes you can use to describe the format of  your date string).

Now let’s insert a date into our table and then query it right back to see what’s in its time component.

So even though we did not specify a time when inserting the date, a time of midnight has been automatically appended to our date (interestingly, if all we do is insert a time into our date column, Oracle will default the date component to the first day of the month).

Personally, I’m a fan of the to_date function and always use it when I’m working with dates; however there’s another way to achieve the same result, and that’s to use something called the ANSI date literal. The date literal must be specified in the following format:

And here’s a rewriting of our insert statement using a date literal:

Like I said, I never use date literals myself, but you might find them more convenient. There’s no accounting for taste.

There are a number of simple operations you can carry out with dates. You can, for example, compare them. Oracle considers a later date to be greater than an earlier date. In other words, Oracle believes that tomorrow is greater than today. Which, I guess, makes them optimists.

You can also subtract dates from each other. This will give you the number of days between those two dates. 

You cannot add two dates together. You also cannot slice them up and fry them in olive oil with chili and cumin. Which might sound like a ridiculous thing for me to say, but if you think about it, trying to add two dates together is just as ridiculous. 

There is one last thing I’d like to mention about the DATE datatype, and that is the sysdate function. A call to sysdate will always return the current date. 

And that in essence is everything you need to know about the DATE datatype. Got that? Great, now there’s one last thing I’d like you to do for me. Forget everything I’ve just taught you. Forget it all, because…

The TIMESTAMP Datatype

I’ve been an Oracle developer for close to 20 years now (I’ll pause and give you a chance to say no way, you don’t look old enough!). Back when I started – a time when dinosaurs still roamed the plains and the earth was flat – the date datatype was all we had. However, as part of Oracle 9i we were gifted the timestamp datatype, and, in truth, I’m surprised that it hasn’t totally supplanted the date datatype by now. It should; it has everything date has and more.

The timestamp datatype is made up of the century, year, month, day, hour, minute and second. But just when you start thinking, “Big deal, DATE did that too,” it whips out it’s joker: it also records fractional seconds.

Admittedly, not all processes are so time-critical that a millisecond here or there makes a difference, but since timestamp is no more cumbersome to use than date you might as well always use it.

Here’s how you specify a timestamp column:

TIMESTAMP [(fractional_seconds_precision)]

The optional fractional_seconds_precision is a number – from 0 to 9 – which tells Oracle how many digits you want to store your fractions of a second.  The default is 6.  

Beyond the fractional seconds, the timestamp datatype is pretty much analogous to the date datatype. “Oh, you’ve got a to_date function?” it says. “Well I’ve got to_timestamp.”

“And did I hear you boasting about your ANSI date literal? Well, check out this shiny new ANSI timestamp literal.”

Let’s add a new column to our table and give this new datatype a spin. 

Let’s add a record using the to_timestamp function:

As you’ve probably guessed, the FF represents the fractional seconds.

Let’s add another record using the timestamp literal. The format is as follows:

So, to add that record we’ll need to say:

The TIMESTAMP WITH TIME ZONE Datatype

Timestamp is impressive, but what if, in addition to that fractional second, you also want to record the timezone? For that you’ll need to use the TIMESTAMP WITH TIME ZONE datatype which, if we’re being honest, is just TIMESTAMP with an extra little trick. Time zones are declared as offsets of the Greenwich Mean Time. Here, let me show you what I mean:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

You can probably already see what the statements above do, but I’ll tell you anyway otherwise I won’t feel like I’m doing my job here. We’ve amended our table to add a new TIMESTAMP WITH TIME ZONE column (please note the syntax). Next we inserted a value into that column using a variation of the timestamp literal that we talked about earlier. 

The TIMESTAMP WITH TIME ZONE data type has its own flavour of the to_timestamp function too. It’s called to_timestamp_tz.  Let’s rewrite our insert statement to use it:

You’ve probably noticed the two new format codes: TZH and TZM. You’ve probably also guessed that they stand for Time Zone Hour and Time Zone Minute. 

Before we move on from timestamps there’s one last function that I want to introduce you to: systimestamp. It’s analogous to sysdate and it returns the current timestamp with time zone. 

I live just outside London (GMT) and it’s just past nine in the evening right now. When I look out of my window, the trees look naked and arthritic, because it is late January.

DATE ARITHMETIC

A little while ago I was making fun of you for wanting to add two dates together. Sorry, that was mean of me. There are ways of adding to a date or a timestamp, and it’s important to know them. If, for instance, I wanted to find out what the date would be 2 days from today I could run the following query:

Or, if I was wondering what the date was 10 days ago:

And since we know that an hour is one-twenty-fourth of a day, we can add and subtract them too. If I want to know what the time will be 3 hours from now.

We can do the same thing for minutes (1/(24 * 60)) and for seconds (1/(24*60*60)) too.

You can also use the handy ADD_MONTHS function to add or subtract whole months. If, for instance, I want to know what the date will be 5 months from now:

Or if I want to know what the date was 23 months ago:

Some other handy date functions are LAST_DAY and NEXT_DAY, which return the last date of the month of the parameterised date, and the date of the next day of the parameterised weekday after the parameterised date respectively.

Source: Oracle

Source: Oracle

INTERVAL

I described the LAST_DAY and NEXT_DAY functions as “handy”. I was lying. In truth, I don’t remember the last time I used either of them. You might need them, but I never have. 

However, when I said that it is important to know how to add to a date that wasn’t a lie. Adding a duration of time – an interval – to a date is something developers are called on to do all the time. As you know, if we want to find out what the date will be in 1 – or even 100 – days one thing we could do is simply add 1 – or 100 – to systimestamp.  However, we could also use the INTERVAL data type. 

Unlike DATE and TIMESTAMP which are records of pinpoints in time, the INTERVAL data type is a measure of an amount of time – 1 day, 100 days, 27 years or even 5 seconds.

There are two INTERVAL data types:

INTERVAL YEAR TO MONTH – which records a duration of years and months

INTERVAL DAY TO SECOND – which records a duration of days, hours, minutes and seconds.

Here’s how they’re specified:

Year_precision is the number of digits in the year field, whereas day_precision is its analog for the day field. 

Let’s add two interval columns to our table.

Intervals can be expressed as literals. If, for example, we wanted to insert an interval of 3 years and 11 months into int_y2d_col it would be:

And if we wanted to insert an interval of 4 days, 3 hours, 2 minutes and 1 second into int_d2s_col, we could use the following literal:

But if you’re like me and you prefer functions to literals, you might want to note these two: TO_YMINTERVAL and TO_DSINTERVAL. The first of the two converts a parameterised string to a YEAR TO MONTH INTERVAL, while the latter does the same for DAY TO SECOND INTERVALs.

If, for instance, we wanted to know what the date would be 17 years and 3 months from today, we might say:

But if we’re being a little more modest and only wanted to know what the date would be in, say, 12 days, 8 hours, and 4 minutes:

CONCLUSION

As we said at the start of this article, time is one of the tripods upon which the Oracle database stands. It’s impossible to get very far without an understanding of how to measure and manipulate it. This article is by no means exhaustive, but I hope that it provides the foundation you might need to go on and learn more. 

You’re welcome.