SQL and Dates


When dates are stored in databases, you will soon get in touch with PostgreSQL and simple date and time operations. SQL has to be used. In the next sections, you learn to work with SQL in dates.

SQL, Dates, and Time Zones

When dealing with time and dates, one of the most crucial aspects is time zones. In general, the world has been divided into 25 integer time zones.

Integer means that the difference from one time zone to the other is a multiple of one hour . Some countries have adopted nonstandard time zones. They use 30-minute increments , which can make working with time zones complicated. Each time zone is 15 ° longitude, as measured east and west from the prime meridian of the world at Greenwich, England. Greenwich, which is a small town near London, is the center of world time. Since 1884, Greenwich has been situated on the prime meridian (0 ° longitude). Every time zone is defined relative to Greenwich Mean Time (GMT). GMT is the same as Zulu Time. The following table lists all world time zones. The asterisks (*) are an international sign indicating that the time zone marks half-hour steps.

GMT Zone Military Civilian Time Zones Cities
GMT Z Zulu

GMT: Greenwich Mean

UT: Universal

UTC: Universal Co-ordinated

WET: Western European

London, England Dublin, Ireland Edinburgh, Scotland Lisbon, Portugal Reykjavik, Iceland Casablanca, Morocco
GMT+1 A Alpha CET: Central European Paris, France Berlin, Germany Amsterdam, The Netherlands Brussels, Belgium Vienna, Austria Madrid, Spain Rome, Italy Bern, Switzerland Stockholm, Sweden Oslo, Norway
GMT+2 B Bravo EET: Eastern European Athens, Greece Helsinki, Finland Istanbul, Turkey Jerusalem, Israel Harare, Zimbabwe
GMT+3 C Charlie BT: Baghdad Kuwait Nairobi, Kenya Riyadh, Saudi Arabia Moscow, Russia
GMT+3:30 C*     Tehran, Iran
GMT+4 D Delta   Abu Dhabi, UAE Muscat Tblisi Volgograd Kabul
GMT+4:30 D*     Kabul, Afghanistan
GMT+5 E Echo    
GMT+5:30 E*     India
GMT+6 F Foxtrot    
GMT+6:30 F*     Cocos Islands
GMT+7 G Golf WAST: West Australian Standard  
GMT+8 H Hotel CCT: China Coast  
GMT+9 I India JST: Japan Standard,  
GMT+9:30 I*   Australia Central Standard Darwin, Australia Adelaide, Australia
GMT+10 K Kilo GST: Guam Standard  
GMT+10:30 K*     Lord Howe Island
GMT+11 L Lima    
GMT+11:30 L*     Norfolk Island
GMT+12 M Mike

IDLE: International Date Line East

NZST: New Zealand Standard

Wellington, New Zealand Fiji Marshall Islands
GMT+13:00 M*     Rawaki Islands: Enderbury Kiribati
GMT+14:00 Kiritibati M ±     Line Islands:
GMT- “1 N November WAT: West Africa Azores, Cape Verde Islands
GMT- “2 O Oscar AT: Azores  
GMT- “3 P Papa   Brasilia, Brazil Buenos Aires, Argentina Georgetown, Guyana
GMT- “3:30 P*     Newfoundland
GMT- “4 Q Quebec AST: Atlantic Standard Caracas La Paz
GMT- “5 R Romeo EST: Eastern Standard. Bogota Lima, Peru New York, NY, USA
GMT- “6 S Sierra CST: Central Standard Mexico City, Mexico Saskatchewan, Canada
GMT- “7 T Tango MST: Mountain Standard.  
GMT- “8 U Uniform PST: Pacific Standard Los Angeles, CA, USA
GMT- “8:30 U*      
GMT- “9 V Victor YST: Yukon Standard.  
GMT- “9:30 V*      
GMT- “10 W Whiskey

AHST: Alaska- “Hawaii Standard

CAT: Central Alaska

HST: Hawaii Standard

 
GMT- “11 X X-ray NT: Nome.  
GMT “12 Y Yankee IDLW: International Date Line West  

Some countries do not use the same time for the whole year. Most European countries use winter time and summer time (daylight saving time). Daylight saving time supposedly enables people to use the day more efficiently . IT people usually don't care, because they use the non-daylight hours anyway, to write books or to do some wonderful hacking.

PostgreSQL offers a lot of data types for storing time and dates, and it is important to know how dates, time, and time zones are treated by the database engine. The following sections present a short overview of all datatypes available.

timestamptimestamp is used to store the date and time. timestamp is the most precise datatype available; time can be stored up to a precision of 1 microsecond. timestamp can store dates from BC 4713 to AD 1,465,001, which should be enough for "ordinary" applications. Eight bytes of storage are required to store a timestamp.

timestamp with time zone

Sometimes it is necessary to store the time zone in combination with a certain date and time. Because an ordinary timestamp does not support time zones, PostgreSQL offers a datatype called timestamp with time zone to do the job. Both timestamp and timestamp with time zone need 8 bytes of storage, but because some space is needed to store the time zone, timestamp with time zone supports only a smaller range of valid dates. Starting with AD 1903, timestamp with time zone can store dates until AD 2037 ”with a resolution of 1 microsecond.

Inserting a timestamp into a table is simple, because PostgreSQL supports ISO-compliant time stamps. To show you how it can be done, we create a table with just one column containing a timestamp first:

 CREATE TABLE showstamp(mystamp timestamp with time zone); 

The table has been created successfully:

 Table "showstamp"  Attribute            Type            Modifier -----------+--------------------------+----------  mystamp    timestamp with time zone 

We can insert a value into the table using the following:

  INSERT INTO showstamp VALUES('2001-06-25 09:22:12 -8:00');  

The INSERT statement inserts June 26, 2001 into the database. The time is set to 9 o'clock, 22 minutes, and 12 seconds. The time zone is set to PST (Pacific Standard Time).

Let's select the data from the table now. We use a simple SELECT * to retrieve the records:

 performance=#  SELECT * FROM showstamp;  mystamp ------------------------  2001-06-25 19:22:12+02 (1 row) 

At first sight it looks strange , but remember that the database displays the time for the time zone you are in currently. At the moment, I am working in Vienna, which is in time zone GMT+1. Because we have summer time in Austria now, we have to use Eastern European time (Eastern Europe is one hour before Austrian winter time). The time zone we are currently in is consequently GMT+2. We have inserted the record in Pacific Standard Time, which is actually 10 hours behind Eastern European time. Therefore 19 o'clock is displayed instead of 9 o'clock. PostgreSQL adds the time zone to the result.

You do not have to worry about time zones on the database level, but it might be a little tricky on the application level. To get around the problem, you use modules, which already exist when you write your own applications.

In the example, we have not defined the amount of microseconds in the timestamp . We could have done it the following way:

  INSERT INTO showstamp VALUES('2001-06-25 19:22:12.32 +2:00');  

PostgreSQL now adds 32 microseconds to the timestamp . Because only a few applications need microseconds, you can omit them when inserting the data into the database.

If you are working with a timestamp , you can insert only a date; you do not have to define the time:

  INSERT INTO showstamp VALUES('2001-03-2');  

Now the date is set to February 2, 2001. If we query the table, we get the following result:

 performance=#  SELECT * FROM showstamp;  mystamp ---------------------------  2001-06-25 19:22:12.32+02  2001-03-02 00:00:00+01 (2 rows) 

The time in the second record is set to a default value. In this case, PostgreSQL uses midnight in Central Europe as the default value.

Currently, PostgreSQL offers three ways to define the time zone. The following INSERT statements insert the same data into the database:

  INSERT INTO showstamp VALUES('2001-06-25 19:22:12.32 +2:00');   INSERT INTO showstamp VALUES('2001-06-25 19:22:12.32 +2');   INSERT INTO showstamp VALUES('2001-06-25 19:22:12.32 +200');  

We perform a simple query to check whether the data in the table is always the same:

 performance=#  SELECT * FROM showstamp;  mystamp ---------------------------  2001-06-25 19:22:12.32+02  2001-06-25 19:22:12.32+02  2001-06-25 19:22:12.32+02 (3 rows) 

You can see that it works perfectly well.

interval

Sometimes it might be useful to store intervals. The syntax of interval can sometimes be a little tricky on the application level, but it is easy to get used to it. PostgreSQL needs 12 bytes of storage to store an interval . The precision is 1 microsecond, as it is for " timestamp ." The range of valid dates extends from 178,000,000 BC to 178,000,000 AD.

Inserting intervals into a database might be slightly more difficult than inserting a timestamp . To show you, how intervals have to be treated, we create a table with one column:

  CREATE TABLE showinter(mystamp interval);  

The table has now successfully been created and we can insert some data:

  INSERT INTO showinter VALUES('1 year 8 months 3 days 12 hours 9 minutes 3 seconds');  

If the INSERT statement succeeds, we can find the record in the table:

 performance=#  SELECT * FROM showinter;  mystamp -------------------------------  1 year 8 mons 3 days 12:09:03 (1 row) 

If we want to insert 12 hours, 9 minutes, and 3 seconds, we can also do it the following way:

  INSERT INTO showinter VALUES('12:9:3');  

If we query the table now, we can see that the record is displayed pretty much the same way that we inserted it:

 performance=#  SELECT * FROM showinter;  mystamp ----------  12:09:03 (1 row) 

Before we get to some additional examples, let's look at the syntax used to define interval :

 Quantity Unit [Quantity Unit...] [Direction] 

Quantity is an integer value, which can also be negative (for example, 12 , 23 , and so forth). Units are seconds, minutes, hours, days, weeks, months, years , decades, centuries, or millennia.

Direction can be ago or simply empty. The following example shows how ago can be used:

  INSERT INTO showinter VALUES('12:9:3 ago');  

ago tells the database that the value we define in the INSERT statement is negative. If we query showinter after performing the INSERT statement, it looks like this:

 performance=#  SELECT * FROM showinter;  mystamp -----------  -12:09:03 (1 row) 

date

For many applications, storing a timestamp is overkill and wastes too much space. You can use date to store dates without time. Therefore, the precision is one day. Four bytes of storage are used to store values from BC 4713 to AD 32,767.

You should use date instead of timestamp if you are 100% sure that you will never store time in combination with date , because date requires only half the storage " timestamp " needs.

PostgreSQL supports a variety of methods to insert dates into the database. One way is to use the ISO-8601 format. This format is also used to define the date when inserting a timestamp into the database.

Let's create a simple table:

  CREATE TABLE showdate(mydate date);  

Now we can insert June 25, 2001 in various ways:

  INSERT INTO showdate VALUES('2001-06-25');   INSERT INTO showdate VALUES('June 25, 2001');   INSERT INTO showdate VALUES('20010625');   INSERT INTO showdate VALUES('010625');   INSERT INTO showdate VALUES('June 25, 2001 AD');  

All these INSERT statements insert the same data into the database. We can easily check this by performing a simple full table scan:

 performance=#  SELECT * FROM showdate;  mydate ------------  2001-06-25  2001-06-25  2001-06-25  2001-06-25  2001-06-25 (5 rows) 

PostgreSQL also offers two other ways of defining a date. We don't recommend these ways, because they can lead to a lot of confusion. Suppose you want to insert June 5, 2001 into the database. Here are two SQL statements that can be used to do the job:

  INSERT INTO showdate VALUES('5/6/2001');   INSERT INTO showdate VALUES('6/5/2001');  

The first SQL statement uses the U.S. style; the second statement uses the European style. You can configure which style to use, but use a syntax that points out clearly whether you mean June 5, 2001 or May 6, 2001.

time [ without time zone ]

PostgreSQL offers a datatype called time to store a certain time. time without time zone does not store the time zone. The range of values lasts from 00:00:00.00 to 23:59:59.99; in other words, all values from midnight to midnight can be saved by using time . The precision is 1 microsecond again, and PostgreSQL uses 4 bytes of storage.

time [ with time zone]time with time zone is similar to time without time zone . The only difference is that time is stored in combination with the time zone. The valid range is therefore from 00:00:00.00+12 to 23:59:59.99 “12. Four bytes of storage are used.

Inserting dates into a database is fairly easy. Here are two examples:

  CREATE TABLE showtime(mydate time with time zone);   INSERT INTO showtime VALUES('12:22:07.04+1');   INSERT INTO showtime VALUES('12:22:07.04');  

We create a table and insert two records into the table. In the first example, we use Central European time explicitly. In the second one, we do not define a time zone and PostgreSQL uses the default value.

If we perform a full table scan, we receive the following result:

 performance=#  SELECT * FROM showtime;  mydate -------------  12:22:07+01  12:22:07+02 (2 rows) 

The table contains two records. The second record uses the default time zone of the system, which, in our case, is Eastern European time.

PostgreSQL offers more methods to deal with time. The next two examples show you how time can be inserted:

  INSERT INTO showtime VALUES('0:22:07 PM');   INSERT INTO showtime VALUES('122207');  

Both INSERT statements tell the database to insert 12 o'clock, 22 minutes, and 7 seconds into the database.

Let's check whether the records have been inserted correctly:

 performance=#  SELECT * FROM showtime;  mydate -------------  12:22:07+02  12:22:07+02 (2 rows) 

The result looks good ”the records are identical.

Special Values and Abbreviations

PostgreSQL supports abbreviations for special dates and times. These abbreviations can be useful and make your software easier to read and easier to understand.

One of the most important values is the "birthday" of Unix systems. In general, the 0 time of a Unix system is set to January 1, 1970. In PostgreSQL, this event is called epoch . Use epoch instead of 1970-01-01 00:00:00+00 . The next example shows that there is no difference between the two:

  CREATE TABLE specials(mydate timestamp with time zone);   INSERT INTO specials VALUES('epoch');  

We have created a table and inserted epoch into it. Let's perform a simple query:

 performance=#  SELECT * FROM specials WHERE mydate='1970-01-01 00:00:00+00';  mydate --------  epoch (1 row) 

epoch is retrieved because it is equal to Unix starting time.

Another important function when working with dates is now . In the next example, now returns the current time. To be more precise, now returns the current transaction time:

 performance=#  SELECT now();  now ------------------------  2001-05-12 15:04:15+02 (1 row) 

In many applications, it might be useful for you to find out today's date. PostgreSQL offers special abbreviations for today , yesterday , and tomorrow . The following shows how these abbreviations can be used in INSERT statements:

  INSERT INTO specials VALUES('today');   INSERT INTO specials VALUES('tomorrow');   INSERT INTO specials VALUES('yesterday');  

When we retrieve the data, we can see that PostgreSQL uses midnight as the time:

 performance=#  SELECT * FROM specials;  mydate ------------------------  2001-05-12 00:00:00+02  2001-05-13 00:00:00+02  2001-05-11 00:00:00+02 (3 rows) 

If you want to compare dates, you might find the next three abbreviations useful. Sometimes you have to set the time to the latest or the earliest moment available. PostgreSQL offers the appropriate abbreviations:

  INSERT INTO specials VALUES('infinity');   INSERT INTO specials VALUES('-infinity');  

We use infinity and -infinity , to define the last earliest moment in the PostgreSQL's time system:

 performance=#  SELECT * FROM specials;  mydate -----------  infinity  -infinity (2 rows) 

Time can also be an undefined value. In this case, you can use NULL or invalid .

When defining dates, the month can be defined as either a number (for example, 1 for January) or a string. Here is a list of all abbreviations accepted by PostgreSQL:

Month Abbreviation
January Jan
February Feb
March Mar
April Apr
May May
June Jun
July Jul
August Aug
September Sep , Sept
October Oct
November Nov
December Dec

The abbreviations can be used instead of the name of a month. Shortcuts are also available for the day of the week:

Day of the wWeek Abbreviation
Monday Mon
Tuesday Tue, Tues
Wednesday Wed, Weds
Thursday Thu, Thur, Thurs
Friday Fri
Saturday Sat
Sunday Sun

Performing Simple Date and Time Operations with SQL

Many of you might already have been in crucial situations where you were not sure how certain operations related to dates could be performed. Sometimes working with dates can be tricky, because you can't find documentation about what is really going on inside a database and how operators are defined for certain datatypes. PostgreSQL offers a good onboard documentation system, but in most cases examples are easier to understand than syntax overviews.

Performing calculations with dates and time has always been complicated. This section presents real solutions for how to work with dates efficiently in PostgreSQL.

Casting is one of the most important tasks when dealing with dates. Currently, PostgreSQL users have a choice of three casting methods.

A function called cast can be used to convert one datatype to another. The syntax of cast is simple. In the following example, you can see how timestamp is converted to date :

 performance=#  SELECT CAST(now() AS date);  ?column? ------------  2001-05-12 (1 row) 

The second method is to use the :: operator. In the next example, we perform the same operation as above, but this time we use the :: operator:

 performance=#  SELECT now()::date;  ?column? ------------  2001-05-12 (1 row) 

The :: operator can be used several times per query:

 performance=#  SELECT now()::date::text;  ?column? ------------  2001-05-12 (1 row) 

In this example, we convert the result of now to date first. After that, we cast the result of the first conversion to text.

Sometimes it also is possible to use the name of the datatype that we want to cast a value to as a function. In the next example, we cast the result of now to text using a function called text :

 performance=#  SELECT text(now());  text ------------------------  2001-05-12 16:56:39+02 (1 row) 

One of the most important datatypes when working with dates and times is reltime , which is a limited-range time interval. It also can be called Unix delta time . Every time we add or subtract time or date from a value, we can use the datatype reltime .

In the next example, we want to add 30 days to June 12, 2001:

 performance=#  SELECT '2001-06-12 00:00:00' + ('30 days'::reltime);  ?column? ------------------------  2001-07-12 00:00:00+02 (1 row) 

First we define the timestamp to which we want to add a certain amount of time. Then we define the amount of time we want to add and cast it to reltime . The + operator is defined for timestamp and reltime so the operation can be performed.

If we want to make sure that the datatype of the output is what we want it to be, we can cast the result explicitly. In the next example, we cast the result to timestamp :

 performance=#  SELECT timestamp('2001-06-12 00:00:00' + ('30 days'::reltime));  timestamp ------------------------  2001-07-12 00:00:00+02 (1 row) 

If we look closer at the result of the query, we can see that exactly 1 month has been added, because June is 30 days long. We could have achieved the same result by using a query like this:

 performance=#  SELECT date('2001-06-12 00:00:00' + ('1 month'::reltime));  date ------------  2001-07-12 (1 row) 

The operations we have just described have been tested on PostgreSQL 7.1. Users of PostgreSQL 7.02 or below might face some troubles with queries such as these. The syntax is right for PostgreSQL 7.02 as well, but PostgreSQL cannot process it correctly. This problem occurs because there is no explicit date+reltime math operator. There is an operator defined for date+int , which assumes that the integer value is counted in days. Additionally, there is a binary-compatible entry for reltime->int . To help you understand what is going on inside the database, here is a short overview of how the error occurs: PostgreSQL 7.02 casts the result of '30 days'::reltime to integer and adds it to timestamp . Seconds should be returned, but the database thinks that it was days and an error occurs. This error shows that working with dates and time is always tricky. You should test a query extensively before you include it in your application. The problem we described previously has already been fixed.

Let's try another query:

 performance=#  SELECT date('2001-06-12 00:00:00' + '30 days');  ERROR:  Bad date external representation 'e' 

This query fails because PostgreSQL does not know what to do with '30 days' ; we have to perform an explicit cast to reltime . In the next example, we perform the same operation that we performed before, but this time we want the result to be displayed as date :

 performance=#  SELECT date('2001-06-12 00:00:00' + ('30 days'::reltime));  date ------------  2001-07-12 (1 row) 

Sometimes people try to add dates, but operations like that don't make sense. Here is a query example:

 performance=#  SELECT date('2001-06-12'::date + '2001-06-12'::date);  ERROR:  Unable to identify an operator '+' for types 'date' and 'date'         You will have to retype this query using an explicit cast 

The syntax of the query is okay, but what do we expect as the result of the operation? The result of adding two date s is not defined, so PostgreSQL does not support the + operator for adding two dates.

+ is defined for timestamp + int . In the next example, you can see the result of the addition of two timestamp s:

 performance=#  SELECT timestamp('2001-06-12'::timestamp + 1);  timestamp ------------------------  2001-06-13 00:00:00+02 (1 row) 

PostgreSQL simply adds one day. If we try to do the same things we have done with an integer value with float , the result is somehow unexpected:

 performance=#  SELECT timestamp('2001-06-12'::timestamp + 1.4);  timestamp ------------------------  2001-06-13 00:00:00+02 (1 row) 

The result is the same when adding 1.4 as when adding 1 (for internal reasons).

The - operator is defined for subtracting two dates:

 performance=#  SELECT date('2001-06-12'::date - '2000-06-01'::date);  date ------------  1970-01-01 (1 row) 

Now we perform the same operation, but cast the result to timestamp :

 performance=#  SELECT timestamp('2001-06-12'::date - '2000-06-01'::date);  timestamp ------------------------  1970-01-01 01:06:16+01 (1 row) 

Sometimes, basic arithmetic operations have to be done with intervals. Here is an example:

 performance=#  SELECT interval('3 days') + interval('1 month');  ?column? --------------  1 mon 3 days (1 row) 

Adding to intervals is an easy process. You can see from the previous example that PostgreSQL displays the expected result. Now we want to subtract 1 month from 3 days :

 performance=#  SELECT interval('3 days') - interval('1 month');  ?column? -----------------  -1 mons +3 days (1 row) 

The result seems a little strange, because PostgreSQL does not seem to calculate the amount of days. If you think about it, you will see the result in a different light. Because a month can have 28, 29, 30, or 31 days, the amount of days cannot be calculated if you don't know how long a month is.

If we cast the result to interval , the output won't change for the reasons we have just described:

 performance=#  SELECT interval(interval('3 days') - interval('1 month'));  interval -----------------  -1 mons +3 days (1 row) 

If we add 1 year to the result of this query, the result looks a little better:

 performance=#  SELECT interval(interval('3 days') - interval('1 month')) +   interval('1 year');  ?column? ----------------  11 mons 3 days (1 row) 

Now PostgreSQL tells us that the result is 11 mons and 3 days . The number of months can be calculated exactly, because a year always has exactly 12 months.

The next example shows how a simple division can be performed:

 performance=#  SELECT interval('30 days')/10;  ?column? ----------  3 days (1 row) 

As we have expected, the result of the query is 3 . But what happens when we divide 1 month by 2 ?

 performance=#  SELECT interval('1 month')/2;  ?column? ----------  15 days (1 row) 

PostgreSQL performs the operation and assumes that a month has 30 days. Therefore the result of the query is 15 .

What happens when we multiply 1 day by 60 ?

 performance=#  SELECT interval('1 day')*60;  ?column? ----------  60 days (1 row) 

The result is 60 days . PostgreSQL does not display 2 months or something like that for the same reason discussed previously ”the number of days in a month is not always the same.

If we multiply 1 month by 13 , PostgreSQL displays the result in a great way:

 performance=#  SELECT interval('1 month')*13;  ?column? --------------  1 year 1 mon (1 row) 

PostgreSQL not only supports basic arithmetic operations. In real-world applications, it might be useful to find out which one of the two intervals is the higher one. Therefore, PostgreSQL provides a function called interval_cmp :

 performance=#  SELECT interval_cmp(interval('3 months'), interval('4 months'));  interval_cmp --------------            -1 (1 row) 

interval_cmp returns 1 if the first value is higher, if both values are equal, and -1 if the second value is higher (the cmp function should be defined for every datatype in PostgreSQL to perform join operations).

interval_cmp is not the only function available. PostgreSQL supports many additional functions. If you need a complete list of those functions, use \df interval :

 performance=#  \   df interval  List of functions    Result        Function              Arguments ------------+------------------+----------------------------  interval    interval          interval  interval    interval          reltime  interval    interval          text  interval    interval          time  interval[]  interval_accum    interval[], interval  interval    interval_avg      interval[]  integer     interval_cmp      interval, interval  interval    interval_div      interval, double precision  boolean     interval_eq       interval, interval  boolean     interval_ge       interval, interval  boolean     interval_gt       interval, interval  integer     interval_hash     interval  interval    interval_larger   interval, interval  boolean     interval_le       interval, interval  boolean     interval_lt       interval, interval  interval    interval_mi       interval, interval  interval    interval_mul      interval, double precision  boolean     interval_ne       interval, interval  interval    interval_pl       interval, interval  time        interval_pl_time  interval, time  interval    interval_smaller  interval, interval  interval    interval_um       interval (22 rows) 

PostgreSQL offers a highly developed system for performing date and time operations. We cannot provide a full overview of all functions and operators available in PostgreSQL in this book, but the list of examples should offer a good start for easily adopting pieces of the sample code.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net