Querying by dateSuppose That We Have The Following Table To Record User Registrations:
Create Table Users
User_id Integer Primary Key,
First_names varchar (50),
Last_name varchar (50) Not null,
Email varchar (100) Not null unique,
- We Encrypt Passwords Using Operating System Crypt Function
Password varchar (30) Not null,
- We Only Need Precision To with One Second
Registration_date TimeStamp (0)
);
- Add Some Sample Data
INSERT INTO USERS
(User_ID, First_Names, Last_name, Email, Password, Registration_Date)
Values
(1, 'Schlomo', 'Mendelowitz', 'Schlomo@mendelowitz.com', '67xui2',
TO_TimeSTAMP ('2003-06-13 09:15:00', 'YYYY-MM-DD HH24: MI: SS'));
INSERT INTO USERS
(User_ID, First_Names, Last_name, Email, Password, Registration_Date)
Values
(2, 'George Herbert Walker', 'Bush', 'FORMER-PRESIDENT@whitehouse.gov', 'KL88Q',
TO_TIMESTAMP ('2003-06-13 15:18:22', 'YYYY-MM-DD HH24: MI: SS'));
Let's query for mind who register during the last day:
Column Email Format A35
Column Registration_Date Format A25
SELECT Email, Registration_Date
From user
WHERE registration_date> current_date - interval '1' DAY;
Email registration_date
------------------------------------- ------------
Schlomo@mendelowitz.com 13-jun-03 09.15.00 am
FORMER-PRESIDENT@whitehouse.gov 13-Jun-03 03.18.22 PM
Note how the registration date comes out in a non-standard format that will not sort lexicographically and that does not have a full four digits for the year. You should curse your database administrator at this point for not configuring Oracle with a more sensible default . You can FIX The Problem for yourself Right Now, However: ALTER session
SET NLS_TIMESTAMP_FORMAT =
'YYYY-MM-DD HH24: MI: SS'
SELECT Email, Registration_Date
From user
WHERE registration_date> current_date - interval '1' DAY;
Email registration_date
------------------------------------- -------
Schlomo@mendelowitz.com 2003-06-13 09:15:00
Former-president@whitehouse.gov 2003-06-13 15:18:22
You CAN Query for Shorter Time Interval:
SELECT Email, Registration_Date
From user
WHERE registration_date> current_date - interval '1' Hour;
Email registration_date
------------------------------------- ------------
Former-president@whitehouse.gov 2003-06-13 15:18:22
SELECT Email, Registration_Date
From user
WHERE registration_date> current_date - interval '1' minute;
No rows selected
SELECT Email, Registration_Date
From user
WHERE registration_date> current_date - interval '1' second;
No rows selected
You can be explicit about how you'd like the timestamps formatted:
Select email, to_char (registration_date, 'day, month dd, yyyy') AS reg_day
From user
ORDER by registration_date;
Email reg_day
------------------------------------- ----------------
Schlomo@mendelowitz.com Friday, June 13, 2003former-president@whitehouse.gov Friday, June 13, 2003
OOPS. Oracle Pads Some of these Fields by Default So That Reports Will Be Lined Up and NEAT. We'll Have To Trim The strings Ourslves:
SELECT
Email,
TRIM (to_char (registration_date, 'day')) || ',' ||
Trim (to_char (registration_date, 'month') || '||
Trim (to_char (registration_date, 'dd, yyyy')) AS reg_day
From user
ORDER by registration_date;
Email reg_day
------------------------------------- -------------
Schlomo@mendelowitz.com Friday, June 13, 2003
Former-president@whitehouse.gov Friday, June 13, 2003
Some Very Weird Thingsone Reason That Oracle May Have Resisted Ansi Date-Time DataTypes and Arithmetic Is That The Can Make Life Very Strange for the Programmer.
Alter session set nls_date_format = 'YYYY-mm-dd';
- OLD
SELECT add_months (to_date ('2003-07-31', 'YYYY-MM-DD'), - 1) from Dual
Add_Months
------------
2003-06-30
- New
SELECT TO_TIMESTAMP ('2003-07-31', 'YYYY-MM-DD') - Interval '1' Month from DUAL
Error At Line 1:
ORA-01839: Date Not Valid for Month Specified
- OLD
Select to_date ('2003-07-31', 'YYYY-MM-DD') - 100 from Dual;
TO_DATE ('2
------------
2003-04-22
- New (Broken)
SELECT TO_TIMESTAMP ('2003-07-31', 'YYYY-MM-DD') - Interval '100' Day from DUAL
Error At Line 1:
ORA-01873: The Leading Precision of the Interval Is Too Small
- New (Note The Extra ")")
SELECT TO_TIMESTAMP ('2003-07-31', 'YYYY-MM-DD') - Interval '100' Day (3) from Dual; TO_TIMESTAMP ('2003-07-31', 'YYYY-MM-DD') - Interval'100'day (3)
-------------------------------------------------- -----------
2003-04-22 00:00:00
Some Profoundly Painful ThingsCalculating time intervals between rows in a table can be very painful because there is no way in standard SQL to refer to "the value of this column from the previous row in the report". You can do this easily enough in an imperative Computer Language, EG, C #, Java, or Visual Basic, That Is Reading Rows from an SQL Database But Doing It Purely in SQL IS Tough.
Let's add a few more rows to our users Table to see how this works.
INSERT INTO USERS
(User_ID, First_Names, Last_name, Email, Password, Registration_Date)
Values
(3, 'osama', 'bin laden', '50kids@aol.com', 'Dieusa',
TO_TIMESTAMP ('2003-06-13 17:56:03', 'YYYY-MM-DD HH24: MI: SS'));
INSERT INTO USERS
(User_ID, First_Names, Last_name, Email, Password, Registration_Date)
Values
(4, 'saddam', 'hussein', 'LivinLarge@saudi-online.net', 'WMD34',
TO_TIMESTAMP ('2003-06-13 19:12:43', 'YYYY-MM-DD HH24: MI: SS'));
Suppose That We're Intested In The avele length of time Between Registrations. With so few rows we could just query all the data out and eyeball it:
SELECT Registration_Date
From user
ORDER by registration_date;
Registration_date
-------------------------
2003-06-13 09:15:00
2003-06-13 15:18:22
2003-06-13 17:56:03
2003-06-13 19:12:43
IF we have a lot of data, however, we'll Need to do a self-join.
Column R1 Format A21
Column R2 Format A21SELECT
U1.REGISTRATION_DATE AS R1,
U2.Registration_date as r2
From users u1, users u2
Where u2.user_id = (Select Min (user_id) from users
WHERE registration_date> u1.registration_date)
ORDER BY R1;
R1 R2
-----------------------------------------
2003-06-13 09:15:00 2003-06-13 15:18:22
2003-06-13 15:18:22 2003-06-13 17:56:03
2003-06-13 17:56:03 2003-06-13 19:12:43
Notice That To Find The "Next Row" for the pairing we are usding the
User_id column, Which We know to be sequential and unique, Rather Than the registration_date column, Which May Not Be unique Because Two Users Could Register At Exactly the Same Time.
Now That We Have Information from Adjacent Rows Paired Up in The Same Report We can begin To Calculate Interval:
Column Reg_gap Format A21
SELECT
U1.REGISTRATION_DATE AS R1,
U2.Registration_date as R2,
u2.Registration_date-u1.registration_date as reg_gap
From users u1, users u2
Where u2.user_id = (Select Min (user_id) from users
WHERE registration_date> u1.registration_date)
ORDER BY R1;
R1 r2 reg_gap
------------------------------------------------ -------------
2003-06-13 09:15:00 2003-06-13 15:18:22 000000000 06:03:22
2003-06-13 15:18:22 2003-06-13 17:56:03 000000000 02:37:41
2003-06-13 17:56:03 2003-06-13 19:12:43 000000000 01:16:40
The Interval for Each Row of The Report Has Come Back AS Days, Hours, Minutes, and Seconds. At this point you'd Expect to becomle to average the interval:
SELECT AVG (Reg_GAP)
From
(SELECT)
U1.REGISTRATION_DATE AS R1,
U2.Registration_date as R2,
u2.Registration_date-u1.registration_date as reg_gap
From users u1, users u2where u2.user_id = (SELECT MIN (user_id) from users
WHERE registration_date> u1.registration_date))))
Error At Line 1:
ORA-00932: Inconsistent DataTypes: Expected Number Got Interval
Oops. Oracle is not smart enough to aggregate time intervals. And sadly there does not seem to be an easy way to turn a time interval into a number of seconds, for example, that would be amenable to averaging. If you figure how Out to do it, please let me know!
SHOULD WE GIVE UP? If you have a strong place you can communication the timestamps to old-style Oracle Dates Through Character Strings BEFORE CREANGHE INTERVALS. THIS WIVE USA RESULT AS A FRACTION OF A Day:
SELECT AVG (Reg_GAP)
From
(SELECT)
U1.REGISTRATION_DATE AS R1,
U2.Registration_date as R2,
To_date (to_char (u2.registration_date, 'YYYY-MM-DD HH24: MI: SS'), 'YYYY-MM-DD HH24: MI: SS'
- to_date (to_char (u1.registration_date, 'YYYY-MM-DD HH24: MI: SS'), 'YYYY-MM-DD HH24: MI: SS')
AS reg_gap
From users u1, users u2
Where u2.user_id = (Select Min (user_id) from users
WHERE registration_date> u1.registration_date))))
AVG (REG_GAP)
----------------
.13836034
IF we're going to continue use this ugly Query We OUGHT to CREATE A View:
Create View Registration_INTERVALS
AS
SELECT
U1.REGISTRATION_DATE AS R1,
U2.Registration_date as R2,
To_date (to_char (u2.registration_date, 'YYYY-MM-DD HH24: MI: SS'), 'YYYY-MM-DD HH24: MI: SS'
- to_date (to_char (u1.registration_date, 'YYYY-MM-DD HH24: MI: SS'), 'YYYY-MM-DD HH24: MI: SS')
AS reg_gap
From users u1, users u2
Where u2.user_id = (Select Min (user_id) from users
WHERE registration_date> u1.registration_date)
NOW We can Calculate The Average Time Interval In Minutes: SELECT 24 * 60 * AVG (REG_GAP) AS AVG_GAP_MINUTES from Registration_INTERVALS
AVG_GAP_MINUTES
---------------
199.238889
Reportinghere's An Example of Using Thae
TO_CHAR FUNCTION An Group by To Generate A Report of Sales by Calendar Quarter:
SELECT TO_CHAR (SHIPPED_DATE, 'YYYY') as saleped_year,
TO_CHAR (SHIPPED_DATE, 'Q') as saleped_quarter,
SUM (Price_charged) AS Revenue
From sh_orders_reportable
Where product_id = 143
And shipped_date is not null
Group by to_char (shipped_date, 'yyyy'), to_char (SHIPPED_DATE, 'Q')
ORDER BY TO_CHAR (SHIPPED_DATE, 'YYYY'), To_CHAR (SHIPPED_DATE, 'Q');
SHIPPED_YAR SHIPPED_QUARTER REVENUE
----------------------------------------------------------------------------------
1998 2 1280
1998 3 1150
1998 4 350
1999 1 210
This is a hint that Oracle has all kinds of fancy date formats (covered in their online documentation). We're using the "Q" mask to get the calendar quarter. We can see that this product started shipping in Q2 1998 and that revenues TRALED OFF IN Q4 1998.
