Oracle: how to add minutes to a timestamp?

Question

I need to add 30 minutes to values in a Oracle date column. I do this in my SELECT statement by specifying

to_char(date_and_time + (.000694 * 31)

which works fine most of the time. But not when the time is on the AM/PM border. For example, adding 30 minutes to 12:30 [which is PM] returns 1:00 which is AM. The answer I expect is 13:00. What's the correct way to do this?

Answer 1

In addition to being able to add a number of days to a date, you can use interval data types assuming you are on Oracle 9i or later, which can be somewhat easier to read,

SQL> ed Wrote file afiedt.buf SELECT sysdate, sysdate + interval '30' minute FROM dual SQL> /  SYSDATE              SYSDATE+INTERVAL'30' -------------------- -------------------- 02-NOV-2008 16:21:40 02-NOV-2008 16:51:40 

Answer 2

All of the other answers are basically right but I don't think anyone's directly answered your original question.

Assuming that "date_and_time" in your example is a column with type DATE or TIMESTAMP, I think you just need to change this:

to_char(date_and_time + (.000694 * 31)) 

to this:

to_char(date_and_time + (.000694 * 31), 'DD-MON-YYYY HH24:MI') 

It sounds like your default date format uses the "HH" code for the hour, not "HH24".

Also, I think your constant term is both confusing and imprecise. I guess what you did is calculate that (.000694) is about the value of a minute, and you are multiplying it by the number of minutes you want to add (31 in the example, although you said 30 in the text).

I would also start with a day and divide it into the units you want within your code. In this case, (1/48) would be 30 minutes; or if you wanted to break it up for clarity, you could write ( (1/24) * (1/2) ).

This would avoid rounding errors (except for those inherent in floating point which should be meaningless here) and is clearer, at least to me.