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…

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: