Manipulating Information By Date

In Texis dates are stored as integers representing an absolute number of seconds from January 1, 1970, Greenwich Mean Time. This is done for efficiency, and to avoid confusions stemming from differences in relative times assigned to files from different time zones. The allowable range of years is 1970 through 2037. Years between 1902 and 1970 may be stored and compared for equality (=) but will not compare correctly using less than (<) and greater than (>).

Counters may also be treated as dates for comparison purposes. They may be compared to date fields or date strings. When compared with dates only the date portion of the counter is considered and the sequence number is ignored.

The comparison operators as given in Table here are used to compare date values, so that dates may be used as qualifying statements in the WHERE clause.

Example: The Strategic Planning and Intelligence Department is responsible for polling online news information on a daily basis, looking for information relevant to Acme's ongoing business. Articles of interest are stored in an archived NEWS table which retains the full text of the article along with its subject, byline, source, and date. The date column is named NDATE, for "News Date", as "date" is a special reserved SQL name and can't be used for column names.

A Date field may be compared to a number representing the number of seconds since 1/1/70 0:0:0 GMT (e.g.: 778876248). It may also be compared to a string representing a human readable date in the format 'YYYY-MM-DD [HH:MM[:SS] [AM|PM]]' (e.g.: '1994-03-05 06:30 pm' or '1994-07-04'). The date string may also be preceded by "begin of" or "end of" meaning the first or last second of a day, respectively.

Enter this query:

SELECT   NDATE, SUBJECT
     FROM     NEWS
     WHERE    NDATE BETWEEN 'begin of 1993-07-30'
                        AND 'end of 1993-07-30' ;

Although the date column is stored with an absolute value, it is converted to the correct relative value when displayed. However, a date assigned to a file is to the second, and to match that time, you must match the same number of seconds. Stating the date as 1993-07-30 refers to a particular second of that day. An article which came in at 2 p.m. would not match in seconds. Thus you state the range of seconds that span the 24 hour period called "'1993-07-30'" by specifying a range between the first to last moment of the day.

In this example, all the articles which were saved from July 30, 1993 are displayed with their subject lines. The date as formatted by Texis when displaying the date column is the format used inside the single quotes. It is put in quotes because it is a text string rather than an absolute value.

Dates are usually used to limit the amount of text retrieved based on some other search requirement, and would be so used along with other qualifying statements in the WHERE clause. The next query is identical to the last, but it adds another requirement.

SELECT   NDATE, SUBJECT
     FROM     NEWS
     WHERE    NDATE BETWEEN 'begin of 1993-07-30'
                        AND 'end of 1993-07-30'
     AND      BODY LIKE 'bill gates' ;

Now we can retrieve articles from July 30, 1993, but only a list of those articles whose text body mentions Bill Gates. A listing of Date and Subject of the article will be displayed, as dictated in SELECT. Now we know which articles are available and can pick any we would want to read in full.

This example uses a text query to find sentences in the body of the information with reference to "Bill Gates". Use of this type of query in the LIKE clause is explained in Chapter here. The following articles are retrieved:

NDATE                SUBJECT
  1993-30-07 04:46:04  High-Technology R&D Has Lost Its Cost-Effect...
  1993-30-07 13:10:08  Heavy R&D Spending No Longer the Magic Route...

Date fields can use any of the comparison operators as shown in Table here to manipulate information. We could broaden the date range of this search by increasing the BETWEEN range, or we could do it as follows:

SELECT   NDATE, SUBJECT
     FROM     NEWS
     WHERE    BODY LIKE 'bill gates'
     AND      NDATE > 'begin of 1993-07-30'
     AND      NDATE < 'end of 1993-08-01' ;

Remember that the actual value of the date is in a number of seconds. Therefore, greater than (>) translates to "a greater number of seconds than the stated value", and therefore means "newer than", while lesser than (<) translates to "a fewer number of seconds than the stated value", and therefore means "older than".

This would increase the output list to include dates in the specified range; that is, between July 30th and August 1st 1993.

NDATE       SUBJECT
  1993-07-30 04:46:04  High-Technology R&D Has Lost Its Cost-Effect...
  1993-07-30 13:10:08  Heavy R&D Spending No Longer the Magic Route...
  1993-07-31 07:56:44  Microsoft-Novell battle out in the open
  1993-07-31 16:40:28  Microsoft to Undergo Justice Department Scrutiny
  1993-08-01 09:50:24  Justice Dept. Reportedly to Study Complaints ...

Date strings have some additional operators, "today" and "now". When used following DATE they are converted to today's date and time in seconds for both "today" and "now". A time period of seconds, minutes, hours, days, weeks, or months, can also be specified. A leading plus (+) or minus (-) may also be specified to indicate past or future. Using our example from the NEWS table, the form of the command would be:

SELECT   NDATE, SUBJECT
     FROM     NEWS
     WHERE    NDATE > '-7 days' ;

This query requests all articles less than seven days old and would produce a list of their subjects and date.

SELECT   NDATE, SUBJECT
     FROM     NEWS
     WHERE    NDATE < '-1 minute'
       AND    NDATE > '-1 hour' ;

This query would produce a list of articles which came in over the last hour. The date must be older than 1 minute ago, but newer than 1 hour ago.