{"id":73079,"date":"2017-02-02T16:00:14","date_gmt":"2017-02-02T16:00:14","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-for-absolute-beginners-date-timestamp-and-interval\/"},"modified":"2021-07-14T13:06:56","modified_gmt":"2021-07-14T13:06:56","slug":"oracle-for-absolute-beginners-date-timestamp-and-interval","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-for-absolute-beginners-date-timestamp-and-interval\/","title":{"rendered":"Oracle for Absolute Beginners: Date, Timestamp and Interval"},"content":{"rendered":"

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<\/code> – strings are really just strings, and numbers are really just numbers. But dates \u2014 dates are interesting.<\/p>\n

In this article I’ll talk to you about dates, about time, and about how both are captured and calculated in an Oracle database.\u00a0<\/p>\n

The DATE Datatype<\/h5>\n

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

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\u00a0to_date\u00a0<\/em>function.<\/p>\n

Let’s start by creating a table with a date column.<\/p>\n

CREATE TABLE test_table (\r\ndate_col DATE\r\n);<\/pre>\n
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?<\/p>\n
It resolves this conundrum with the to_date<\/code> function. The to_date<\/code> 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:<\/p>\n
to_date('21 January 2017','DD Month YYYY')<\/pre>\n
to_date('21\/01\/2017','DD\/MM\/YYYY')<\/pre>\n
It’s as simple as that (by the way, here’s a short list of the codes<\/a> you can use to describe the format of \u00a0your date string).<\/p>\n
Now let’s insert a date into our table and then query it right back to see what’s in its time component.<\/p>\n
INSERT INTO test_table(date_col)\r\nVALUES (to_date('21\/01\/2017','DD\/MM\/YYYY'));<\/pre>\n
SELECT to_char(date_col,'DD\/MM\/YYYY HH24:MI:SS') \"test date\"\r\nFROM test_table;\r\n\r\ntest date\r\n------------------------\r\n21\/01\/2017 00:00:00<\/pre>\n
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\u00a0time<\/em> into our date column, Oracle will default the date component to the first day of the month).<\/p>\n
Personally, I’m a fan of the to_date<\/code> 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:<\/p>\n
DATE 'YYYY-MM-DD'<\/pre>\n
And here’s a rewriting of our insert statement using a date literal:<\/p>\n
INSERT INTO test_table (date_col)\r\nVALUES (DATE '2017-01-21');<\/pre>\n
Like I said, I never use date literals myself, but you might find them more convenient. There’s no accounting for taste.<\/p>\n
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.<\/p>\n
TO_DATE('22.JAN.2017','DD.MON.YYYY') > TO_DATE('22.JAN.2017','DD.MON.YYYY')<\/pre>\n
You can also subtract dates from each other. This will give you the number of days between those two dates.\u00a0<\/p>\n
SELECT TO_DATE('21 JANUARY 2017','DD MONTH YYYY') - DATE '2017-01-01' \"date diff\"\r\n FROM DUAL;\r\n\r\ndate diff\r\n------------\r\n20<\/pre>\n
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.\u00a0<\/p>\n
There is one last thing I’d like to mention about the DATE<\/code> datatype, and that is the sysdate<\/code> function. A call to sysdate<\/code> will always return the current date.\u00a0<\/p>\n
And that in essence is everything you need to know about the DATE<\/code> 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…<\/p>\n
The TIMESTAMP Datatype<\/h5>\n
I’ve been an Oracle developer for close to 20 years now (I’ll pause and give you a chance to say\u00a0no way, you don’t look old enough!<\/em>). Back when I started – a time when dinosaurs still roamed the plains and the earth was flat – the date<\/code> datatype was all we had. However, as part of Oracle 9i we were gifted the timestamp<\/code> datatype, and, in truth, I’m surprised that it hasn’t totally supplanted the date<\/code> datatype by now. It should; it has everything date<\/code> has and more.<\/p>\n
The timestamp<\/code> datatype is made up of the century, year, month, day, hour, minute and second. But just when you start thinking,\u00a0“Big deal, DATE did that too,”<\/em> it whips out it’s joker: it also records\u00a0fractional seconds.<\/em><\/p>\n
Admittedly, not all processes are so time-critical that a millisecond here or there makes a difference, but since timestamp<\/code> is no more cumbersome to use than date<\/code> you might as well always use it.<\/p>\n
Here’s how you specify a timestamp<\/code> column:<\/p>\n
TIMESTAMP [(fractional_seconds_precision)<\/em>]<\/p>\n
The optional\u00a0fractional_seconds_precision<\/em> is a number – from 0 to 9 – which tells Oracle how many digits you want to store your fractions of a second. \u00a0The default is 6. \u00a0<\/p>\n
Beyond the fractional seconds, the timestamp<\/code> datatype is pretty much analogous to the date<\/code> datatype. “Oh, you’ve got a to_date<\/code> function?” it says. “Well I’ve got to_timestamp<\/code>.”<\/p>\n
“And did I hear you boasting about your ANSI date literal? Well, check out this shiny new ANSI timestamp literal.”<\/p>\n
Let’s add a new column to our table and give this new datatype a spin.\u00a0<\/p>\n
ALTER TABLE test_table \r\n ADD timestamp_col TIMESTAMP(2);<\/pre>\n
Let’s add a record using the to_timestamp<\/code> function:<\/p>\n
INSERT INTO test_table (timestamp_col)\r\nVALUES ( to_timestamp('21\/01\/2017 12:34:56.78','DD\/MM\/YYYY HH24:MI:SS.FF'));<\/pre>\n
As you’ve probably guessed, the\u00a0FF<\/em> represents the fractional seconds.<\/p>\n
Let’s add another record using the timestamp literal. The format is as follows:<\/p>\n
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'<\/pre>\n
So, to add that record we’ll need to say:<\/p>\n
INSERT INTO test_table (timestamp_col)\r\n VALUES ( TIMESTAMP '2017-01-21 12:34:56.78');<\/pre>\n
The TIMESTAMP WITH TIME ZONE Datatype<\/h5>\n
Timestamp<\/code> is impressive, but what if, in addition to that fractional second, you also want to record the\u00a0timezone<\/em>? For that you’ll need to use the TIMESTAMP WITH TIME ZONE<\/code> datatype which, if we’re being honest, is just TIMESTAMP<\/code> with an extra little trick. Time zones are declared as offsets of the Greenwich Mean Time. Here, let me show you what I mean:<\/p>\n
TIMESTAMP [(fractional_seconds_precision)<\/em>] WITH TIME ZONE<\/p>\n
ALTER TABLE test_table\r\nADD timestamp_tz_col TIMESTAMP (2) WITH TIME ZONE;<\/pre>\n
INSERT INTO test_table (timestamp_tz_col)\r\nVALUES (TIMESTAMP '2017-01-21 21:05:53.46 +02:00');<\/pre>\n
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<\/code> 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.\u00a0<\/p>\n
The TIMESTAMP WITH TIME ZONE<\/code> data type has its own flavour of the to_timestamp<\/code> function too. It’s called to_timestamp_tz<\/code>. \u00a0Let’s rewrite our insert statement to use it:<\/p>\n
INSERT INTO test_table (timestamp_tz_col)\r\nVALUES (to_timestamp_tz('2017-01-21 21:05:53.46 +02:00',\r\n                        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');<\/pre>\n
You’ve probably noticed the two new format codes: TZH<\/em> and TZM<\/em>. You’ve probably also guessed that they stand for Time Zone Hour and Time Zone Minute.\u00a0<\/p>\n
Before we move on from timestamps there’s one last function that I want to introduce you to: systimestamp<\/code>. It’s analogous to sysdate<\/code> and it returns the current timestamp with time zone.\u00a0<\/p>\n
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.<\/p>\n
SELECT systimestamp\r\nFROM dual;\r\n\r\nsystimestamp\r\n-------------------------------------\r\n21-JAN-2017 21.32.48.269997 AM +00:00<\/pre>\n
DATE ARITHMETIC<\/h5>\n
A little while ago I was making fun of you for wanting to add two dates together. Sorry, that was mean of me. There\u00a0are<\/em> 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:<\/p>\n
SELECT systimestamp + 2\r\nFROM dual;<\/pre>\n
Or, if I was wondering what the date was 10 days ago:<\/p>\n
SELECT systimestamp - 10\r\nFROM dual;<\/pre>\n
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.<\/p>\n
SELECT systimestamp + (3\/24)\r\nFROM dual;<\/pre>\n
We can do the same thing for minutes (1\/(24 * 60)) and for seconds (1\/(24*60*60)) too.<\/p>\n
You can also use the handy ADD_MONTHS<\/code> function to add or subtract whole months. If, for instance, I want to know what the date will be 5 months from now:<\/p>\n
SELECT add_months(systimestamp,5)\r\nFROM dual;<\/pre>\n
Or if I want to know what the date was 23 months ago:<\/p>\n
SELECT add_months(systimestamp,-23)\r\nFROM dual;<\/pre>\n
Some other handy date functions are LAST_DAY<\/code> and NEXT_DAY<\/code>, 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.<\/p>\n
\"\"<\/p>\n
Source: Oracle<\/a><\/em><\/p>\n<\/div>\n
\"\"<\/p>\n
Source: Oracle<\/a><\/em><\/p>\n<\/div>\n
SELECT last_day(TIMESTAMP '2017-01-21 21:32:48') \"last day\",\r\n\u00a0 \u00a0    next_day(TIMESTAMP '2017-01-21 21:32:48', 'FRIDAY') \"next day\"\r\nFROM dual;\r\n\r\nlast day      next day\r\n----------    --------------\r\n31\/01\/2017    27\/01\/2017<\/pre>\n
INTERVAL<\/h5>\n
I described the LAST_DAY<\/code> and NEXT_DAY<\/code> functions as “handy”. I was lying. In truth, I don’t remember the last time I used either of them. You<\/em> might need them, but I never have.\u00a0<\/p>\n
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\u00a0interval\u00a0<\/em>– 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<\/code>. \u00a0However, we could also use the INTERVAL<\/code> data type.\u00a0<\/p>\n
Unlike DATE<\/code> and TIMESTAMP<\/code> which are records of pinpoints in time, the INTERVAL<\/code> data type is a measure of an amount of time – 1 day, 100 days, 27 years or even 5 seconds.<\/p>\n
There are two INTERVAL data types:<\/p>\n
INTERVAL YEAR TO MONTH<\/code> – which records a duration of years and months<\/p>\n
INTERVAL DAY TO SECOND<\/code> – which records a duration of days, hours, minutes and seconds.<\/p>\n
Here’s how they’re specified:<\/p>\n
INTERVAL YEAR [(year_precision)] TO MONTH<\/pre>\n
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]<\/pre>\n
Year_precision<\/em> is the number of digits in the year field, whereas day_precision<\/em> is its analog for the day field.\u00a0<\/p>\n
Let’s add two interval<\/code> columns to our table.<\/p>\n
ALTER TABLE test_table\r\nADD (\r\n     int_y2d_col \u00a0INTERVAL YEAR(2) TO MONTH,\r\n     int_d2s_col INTERVAL DAY(2) TO SECOND\r\n    );<\/pre>\n
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<\/em> it would be:<\/p>\n
INSERT INTO test_table (int_y2d_col)\r\nVALUES (INTERVAL '3-11' YEAR TO MONTH);<\/pre>\n
And if we wanted to insert an interval of 4 days, 3 hours, 2 minutes and 1 second into\u00a0int_d2s_col<\/em>, we could use the following literal:<\/p>\n
INSERT INTO test_table (int_d2s_col)\r\n VALUES (INTERVAL '4 3:02:01' DAY TO SECOND);<\/pre>\n
But if you’re like me and you prefer functions to literals, you might want to note these two: TO_YMINTERVAL<\/code> and TO_DSINTERVAL<\/code>. The first of the two converts a parameterised string to a YEAR TO MONTH INTERVAL<\/code>, while the latter does the same for DAY TO SECOND INTERVALs.<\/p>\n
If, for instance, we wanted to know what the date would be 17 years and 3 months from today, we might say:<\/p>\n
SELECT systimestamp + TO_YMINTERVAL('17-3') \"distant future\"\r\nFROM dual;\r\n\r\ndistant future\r\n----------------------------------------\r\n21-APR-2034 21.53.49.841207000 PM +00:00<\/pre>\n
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:<\/p>\n
SELECT systimestamp + TO_DSINTERVAL('12 8:04:00') \"near future\"\r\nFROM dual;\r\n\r\nnear future\r\n----------------------------------------\r\n03-FEB-2017 06.00.12.210000000 AM +00:00\r\n<\/pre>\n
CONCLUSION<\/h5>\n
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.\u00a0<\/p>\n
You’re welcome.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u2014 dates are interesting. In this article I’ll talk to you about dates, about time, and about how both ar…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48365,48366,48392,48426,124952,48526],"coauthors":[48557],"class_list":["post-73079","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginner","tag-beginners-guide","tag-date","tag-interval","tag-redgate-deploy","tag-timestamp"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73079","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73079"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73079\/revisions"}],"predecessor-version":[{"id":88952,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73079\/revisions\/88952"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73079"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}