Oracle Labs by Yuri Khazin, Oracle DBA

June 6, 2013

On DATE and TIMESTAMP math

Filed under: date and timestamp, Oracle Database — oraclelabs @ 15:38

Ever faced with a need to specify a time range like this: “between first and last day of the previous month”?

So how about this one:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) “Last Day of Prev Month”
from dual;

OK, this was easy. But what is they say “between first and last second of the last day of the previous month”
Well, we just subtract an interval of 1 second:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) – interval ‘1’ second “Last Day of Prev Month”
from dual;

Good, they say. Now how about give us all this in timestamp, not a date type.

Precisely, give us the range “between first moment and the last microsecond of the last day in previous month”. That was a long thing to say.

So here it is, in dollars, cents and microseconds:

select
to_timestamp(add_months(trunc(sysdate,’MM’), -1)) “First moment”
to_timestamp(trunc(sysdate,’MM’)) – INTERVAL ‘0 00:00:00.00001’ DAY TO SECOND(6) “Last moment”
from dual;

Was it a microsecond or millisecond? I always get confused with kilos and pounds…

Blog at WordPress.com.