Ingres Database Reference
Document #: US-13229,EN
------------------------------------------------------------------------------
Major subject: sql Minor subjects: sql_syntax
Keywords: datatypes, tech_notes
Abstract:
Working with Date Fields - Equivalent to INGRES Release
6 Technical Note #8 or Release 5 Note #86.
Expert note:
Working with Date Fields
========================
Overview
--------
INGRES provides a powerful syntax in both SQL and QUEL for working with
dates and time intervals. The purpose of this document is to illustrate
the capabilities of date arithmetic and date functions, using several
examples.
o Background
o Operations and Functions
o Combining Functions
o Summary
Background
----------
A date is a value that is either an absolute date, like "02-sep-1987",
an absolute time, like "23:04:48", an absolute date and time, like "28-
feb-1987 12:01:58", a date interval, like "5 years 14 days", or a time
interval, like "8 hrs". These are output by INGRES as a 25 character
string when date format is used. The default output format for a date
is "dd-mmm-yyyy", and for a date and time it is "dd-mmm-yyyy hh:mm:ss".
Dates can be input in a variety of formats. For the date November 15,
1982, INGRES would accept any one of the following as input: "11/15/82",
"11-15-82", "82.11.15", "15-nov-82". There are other input formats,
listed in your query language reference manuals. Please note that
"today" is a legal input for a date field and will put the current
date in the field.
Absolute time input for 10:30:00 could be "10:30:00", "10:30:00 pst",
or "10:30". For absolute date and time, you can combine any of the
three time formats with one of the following date formats: mm/dd/yy
or dd-mmm-yy. Therefore, there are six possible formats for absolute
date and time. Please note that "now" is a legal input and will put
the current date and time in the field.
Input formats for date intervals and time intervals use these units:
yrs,years,months,mos,days,hours,hrs,minutes,mins,seconds,secs. There-
fore, some sample date interval and time interval inputs are: "5
years","8 mos 14 days","23 hours","13 hrs 38 mins 43 secs".
Date values can range from 1-jan-1582 to 31-dec-2382. Time intervals can
range from -800(max) years to +800(max) years within the specified
range of Date values.
Operations and Functions
------------------------
The following operations and functions are available. Each is given with
an example.
1. absolute - absolute = interval
A parts supplier has a table called "delivery", with columns called
"customer", "placedate","shipdate", and "recvdate". The columns with
column names ending in "date" have INGRES' date format. The query is:
How many days did company XYZ wait from the time it placed its order
until it received the order?
SQL: SELECT x=(delivery.recvdate - delivery.placedate) FROM delivery
WHERE delivery.customer = 'XYZ';
QUEL: RETRIEVE (x=delivery.recvdate - delivery.placedate)
WHERE delivery.customer = "XYZ"
2. absolute - interval = absolute OR absolute + interval = absolute
Using the same table described above, the query is: How many orders
were shipped more than 5 days after they were placed?
SQL: SELECT x=COUNT(d.customer) FROM delivery d
WHERE d.placedate < d.shipdate-'5 days';
QUEL: RANGE OF d IS delivery
RETRIEVE
(x=COUNT(d.customer WHERE d.placedate < d.shipdate-"5 days" ))
3. interval - interval = interval OR interval + interval = interval
A pharmaceutical firm is testing two materials for capsules. There is a
table called "results" with columns containing the drug name, the number
of days the drug remained effective using material #1 for the capsule,
and the number of days for material #2. The columns "mat1" and "mat2"
have INGRES' date format. Dr. Jones wants to know the difference between
the effectiveness of the two materials, for each drug.
SQL: SELECT drug,x=mat1 - mat2 FROM results;
QUEL: RETRIEVE (results.drug,x=results.mat1 - results.mat2)
4. date conversion
INGRES stores dates internally in a 12-byte format, and displays dates
as a 25-character string. INGRES knows it is a date, and allows the
operations and functions being discussed in this document. There are
times, however, when a user stores date information in c, vchar (SQL),
or text (QUEL) fields. How can date arithmetic and functions be used on
such fields?
The date conversion function takes the data in a c, vchar (SQL), or text
(QUEL) field, and gives the internal representation of that date value
used by INGRES. This is useful for manipulating dates entered in fields
with c,vchar, or text formats. The value entered in those fields must
have one of the date formats listed earlier or in the SQL and QUEL
Reference Manuals. If the value cannot be converted to a valid date,
INGRES will issue an error message.
Let's look at a specific use of the date conversion function. Suppose a
company has a table containing project names and their due dates. The
due dates are in a field called "complete" with a format of c10. They've
been entered in standard "mm/dd/yy" format. A query to list all due
dates with a 10 day extension is shown below. The resulting column "x"
will be in INGRES' default dd-mmm-yyyy format for dates.
SQL: select name,x=date(projects.complete)+'10 days' from projects;
QUEL: retrieve (projects.name,x=date(projects.complete)+"10 days")
5. DOW conversion
The DOW (day of the week) conversion function gives the day of the week
for the provided date value.
For example, an administrative assistant is keeping track of complaints
called in on a hot-line. He logs each call in an INGRES table which has
a column called 'calldate'. He wants to see the distribution of calls by
the day of the week:
SQL: Two steps are required.
1) CREATE TABLE tempd AS SELECT x=DOW(calls.calldate) FROM calls;
2) SELECT x,y=COUNT(x) FROM tempd GROUP BY x;
QUEL: RETRIEVE (x=DOW(calls.calldate),
y=COUNT(DOW(calls.calldate) BY DOW(calls.calldate)))
6. DATE_TRUNC function
The DATE_TRUNC function returns the first date within a specified unit
of measurement. For example, if the unit of measurement is "month", then
the DATE_TRUNC function for any date of a particular month will return the
first day of that month as a value. The syntax is: DATE_TRUNC(UNIT,DATE).
The DATE_TRUNC function is useful for grouping dates. For example,
suppose a table called "receipts" lists the amount collected in a column
called "amount", and the day it was collected in a column called
"collected". You want to know how much was collected each week. The follow-
ing query will show the starting day of the week, i.e. Monday's date,
and the amount collected that week:
SQL: Two steps are required
1) CREATE TABLE temptable AS
SELECT x=DATE_TRUNC('weeks',receipts.collected),amount
FROM receipts;
2) SELECT x,y=SUM(amount) FROM temptable GROUP BY x;
QUEL: RETRIEVE (x=DATE_TRUNC("weeks",receipts.collected),
y=SUM(receipts.amount BY
DATE_TRUNC("weeks",receipts.collected)))
7. DATE_PART function
The DATE_PART function returns an integer that is one part of the date.
The syntax is: DATE_PART(UNIT,DATE). For example, if the date field has
the value "29-aug-1971", then DATE_PART("day",datefield) returns a value
of 29. For "week" units, the weeks are numbered from 0 to 52. Week 0 is
assigned to the days before the first Monday of the year. For "quarter"
units, they are numbered from 1 to 4. The date_part function is useful
when using aggregates.
Suppose an opinion research firm sends the same questionnaire four times
a year to four random samples. Each set of questionnaires is mailed on
the first of the month. A table called "responses" has the data for all
four surveys. What is the average number of responses received on each
day of the month?
SQL: Two steps are required.
1. create table tempr as
select idnum,x=date_part('day',responses.received)
from responses;
2. select x,y=count(idnum)/4 from tempr group by x;
QUEL: retrieve (a=date_part("day",responses.received),
x=count(responses.idnum
by date_part("day",responses.received))/4)
8. INTERVAL function
When you use date arithmetic, like "datefld1 - datefld2", the result
looks like "23 days". Suppose you just wanted the number "23"? The
INTERVAL function allows you to obtain the number of time units con-
tained within a specified interval. The syntax is: INTERVAL(UNIT,TIME
INTERVAL). Allowable units include secs, mins, hrs, days, wks, mos,
qtrs, and yrs.
Suppose a date field called "duration" has the interval "2 days 5
hours". Then INTERVAL("hrs",duration) returns 53. Note that the date
field can also be an expression whose result is an interval . For exam-
ple, if a community organization conducting a fund drive wants to enter
some data along with the number of days since the drive began. The field
daynum is an integer field which will contain the number of days.
SQL: INSERT INTO drivedata (contribs,daynum)
VALUES (432.56,INTERVAL('days','today'-date('2/15/87')));
QUEL: APPEND TO drivedata (contribs=432.56,
daynum=INTERVAL("days","today"-date("2/15/87")))
Combining Functions
-------------------
The date functions described above can be combined to produce date cal-
culations of various kinds. For example, suppose you wanted to find the
midpoint between two dates. Given "1/1/87" and "1/30/87", you'd like to
have the calculation return a value of "15-jan-1987".
The query is:
SQL: select x=date('1/1/87')+concat(ascii(int4(interval('days',
date('1/30/87')-date('1/1/87'))/2)),'days') from dept;
QUEL: retrieve (x=date("1/1/87")+concat(ascii(int4(interval("days",
date("1/30/87")-date("1/1/87"))/2)),"days"))
Here's what the query does: First, date arithmetic is used to subtract
the two dates. The result, "30 days", is converted to the number "30"
using the interval function. The number is then divided by 2. That
result is converted to integer format, and then converted to an ascii
string so it can be concatenated to the word "days", resulting in "15
days". The "15 days" is then added to the starting date, using date
arithmetic, to give the final result.
Consider another example. Using the same "delivery" table described ear-
lier, suppose you want to know how many shipments were received on the
first Monday of the month. The query is:
SQL: Two steps are required --
1. create table temp as select recvdate,x=dow(recvdate),
y=date_part('month', recvdate) from delivery;
2. select z=min(recvdate) from temp
where x='Mon' group by y;
QUEL: range of d is delivery
retrieve (d.recvdate)
where d.recvdate=min(d.recvdate by date_part("month",d.recvdate)
where dow(d.recvdate)="Mon")
Summary
-------
Date arithmetic, date functions and date conversions in INGRES provide a
powerful set of tools for making full use of data in date format.
Releases affected: all(all.all) - Releases not affected:
Errors:
Bugs/SIRS:
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William