-
Notifications
You must be signed in to change notification settings - Fork 125
Expand file tree
/
Copy pathtimestamp-day-boundaries.sql
More file actions
54 lines (47 loc) · 1.07 KB
/
timestamp-day-boundaries.sql
File metadata and controls
54 lines (47 loc) · 1.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- timestamp-day-boundaries.sql
-- Jared Still jkstill@gmail.com
-- 2018
--
-- get the beginning and end of the current day as timestamp
-- SQLPlus can manage 9 decimal places as of 12.2
-- PL/SQL however can only do 6 decimal places
prompt
prompt SQL Can do 9 decimal places
prompt
select
cast(trunc(systimestamp) as timestamp) day_begin
, cast(
trunc(
systimestamp
+ numtodsinterval('1','DAY')
)
as timestamp
)
- numtodsinterval('.000000001','SECOND')
day_end
from dual
/
prompt
prompt PL/SQL Can do 6 decimal places
prompt
prompt This example looks at the boundaries for the previous day
prompt
set serveroutput on
declare
i_days_back integer := 1;
b timestamp;
e timestamp;
begin
b := cast(trunc(systimestamp) as timestamp) - numtodsinterval( i_days_back ,'DAY');
e := cast(
trunc(
systimestamp
+ numtodsinterval('1','DAY')
)
as timestamp
) - numtodsinterval( i_days_back ,'DAY')
- numtodsinterval('.000001','SECOND');
dbms_output.put_line('Begin: ' || to_char(b));
dbms_output.put_line(' End: ' || to_char(e));
end;
/