17.1 Dates and Time in SQL

PostgreSQL can easily be used to work with dates and time efficiently. In contrast to many other databases, PostgreSQL provides a set of flexible and powerful data types to handle date and time information, as well as a set of functions.

17.1.1 Data Types for Handling Dates and Time

In this section you will take a closer look at PostgreSQL's data types for handling dates and time.

17.1.1.1 interval

interval is one of the most comfortable and efficient data types supported by PostgreSQL. With the help of this data type, it is possible to store differences between two dates easily. An interval consists of the following components: second, minute, hour, day, week, month, year, decade, century, and millennium. Not all of these components have to be mentioned when using the data type. Let's start with some simple examples:

 phpbook=> SELECT '4 years, 3 months, 9 hours 12 seconds'::interval;         interval -------------------------  4 years 3 mons 09:00:12 (1 row) 

As you can see, you just have to pass a string to the database and cast it to interval. The rest of the operations will be performed by the database. In this example you can see that the way the data is returned differs slightly from the way you have sent it to the database. PostgreSQL accepts many input formats, so it's up to you to decide which way you like best.

Here's the next example:

 phpbook=> SELECT '4 years, 3 months, 59 hours 12 seconds'::interval;             interval --------------------------------  4 years 3 mons 2 days 11:00:12 (1 row) 

One of the most comfortable features of PostgreSQL is that the result is automatically transformed. In the listing you can see that 59 hours has been transformed to 2 days and 11 hours.

The next example shows more precisely how things work:

 phpbook=> SELECT '40 hours'::interval;   interval -------------  1 day 16:00 (1 row) 

However, some conversions cannot be performed:

 phpbook=> SELECT '34 days'::interval;  interval ----------  34 days (1 row) 

34 days cannot be transformed to months and days because a month does not have a fixed length. Therefore PostgreSQL decides to keep the string passed to the database as it is.

Keywords can be passed to the database as singular as well as plural words. The next example shows how centuries are treated:

 phpbook=> SELECT '1 century 2 centuries'::interval;  interval -----------  300 years (1 row) 

PostgreSQL recognizes that the words century and centuries have the same meaning and adds the two components. The result is 300 years because centuries are always converted to years.

17.1.1.2 date

After dealing with intervals in detail, we can take a closer look at dates. As you already saw when working with intervals, there is more than just one way to pass data to PostgreSQL. The same applies to dates. In this section you will get an overview of what you can do with dates and which input formats are accepted by PostgreSQL.

 phpbook=> SELECT '2002/1/13'::date;     date ------------  2002-01-13 (1 row) 

First the year and the month are defined. After that, the day of the month is passed to the database. The result is January 13, 2002. Let's take a look at the next example:

 phpbook=> SELECT '1/13/2001'::date;     date ------------  2001-01-13 (1 row) 

The date is the same again, but this time the month has been passed to the database first. Keep in mind that this format is dangerous because the result varies greatly depending on which local settings you use.

German-speaking people might prefer the German way of defining a date:

 phpbook=> SELECT '13.12.2001'::date;     date ------------  2001-12-13 (1 row) 

The result is December 13, 2002.

In PostgreSQL, the format returned by the database will always be accepted as input as well. Therefore the next format is also valid:

 phpbook=> SELECT '2001-1-13'::date;     date ------------  2001-01-13 (1 row) 

Sometimes you might want to use the name of a month instead of its number. PostgreSQL provides an easy-to-use interface:

 phpbook=> SELECT 'December 9, 2002'::date;     date ------------  2002-12-09 (1 row) 

If you want to define explicitly that the date you are passing to the database is AD, you can add it to the string as shown in the next example:

 phpbook=> SELECT 'December 9, 2002 AD'::date;     date ------------  2002-12-09 (1 row) 

If you want to pass December 9, 2002 before Christ to the database, you can use BC instead of AD:

 phpbook=> SELECT 'December 9, 2002 BC'::date;      date ---------------  2002-12-09 BC (1 row) 

If you don't want to use the long version of the word December, you can use an abbreviation:

 phpbook=> SELECT 'Dec 9, 2002'::date;     date ------------  2002-12-09 (1 row) 

Table 17.1 contains a list of all abbreviations related to months.

Table 17.1. Abbreviations for Months

Month	Short Version
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

PostgreSQL supports other abbreviations besides those for months. There is also a list of abbreviations for the day of the week, as you can see in Table 17.2.

Table 17.2. Abbreviations for Days of the Week

Day	Short Version
Monday	Mon
Tuesday	Tue, Tues
Wednesday	Wed, Weds
Thursday	Thu, Thur, Thurs
Friday	Fri
Saturday	Sat
Sunday	Sun

Some dates are special in a way. To define these dates, PostgreSQL provides a set of keywords that can be used easily and can help you to avoid confusion. One of these keywords is epoch. epoch defines the most important point in the history of human beings, the beginning of the golden age that is also known as the century of Unix:

 phpbook=> SELECT 'epoch'::date;     date ------------  1970-01-01 (1 row) 

January 1, 1970 is defined as Unix starting time, and most systems perform all computations relative to epoch.

Two other important keywords are current and now. Current cannot be used with PostgreSQL 7.2 any more, so we recommend changing your applications as well. Here are two examples where current (with PostgreSQL 7.1.3) and now are used:

 phpbook=> SELECT 'current'::date;     date ------------  2002-01-10 (1 row) phpbook=> SELECT 'now'::date;     date ------------  2002-01-10 (1 row) 

As you can see, today's date is retrieved. The same result can be achieved by using today instead:

 phpbook=> SELECT 'today'::date;     date ------------  2002-01-10 (1 row) 

To compute yesterday's and tomorrow's date, PostgreSQL provides keywords as well:

 phpbook=> SELECT 'tomorrow'::date;     date ------------  2002-01-11 (1 row) phpbook=> SELECT 'yesterday'::date;     date ------------  2002-01-09 (1 row) 

As you have seen, working with dates is an easy task and PostgreSQL provides various input formats.

17.1.1.3 time [with time zone] and time [without time zone]

To work with time, PostgreSQL provides two data types that are similar to each other. One data type is called time with time zone. The second one is called time without time zone.

Depending on whether you want to work with time zones, you can choose which data type satisfies your demands.

Just like other data types, time accepts various input formats and you will learn about these in this section. Here's the first example:

 phpbook=> SELECT '12:16'::time;    time ----------  12:16:00 (1 row) 

Just pass the hour and the minutes to PostgreSQL as a string and cast it to time. The result will be what you expected it to be.

If you want to define seconds and microseconds as well, you can add that level of detail to the time easily:

 phpbook=> SELECT '12:16:32.43'::time;    time ----------  12:16:32 (1 row) 

In this example, you cannot see the number of microseconds in the timestamp. In the next example, the result is cast to time with time zone:

 phpbook=> SELECT '12:16:32.43'::time with time zone;    timetz -------------  12:16:32+01 (1 row) 

This time the time zone is added by PostgreSQL automatically. As you can see, the text has been written one time zone east of Greenwich Mean Time (GMT), which is Central European time (also known as Alpha time or CET).

If you need Charlie time (GMT+3, used in Kuwait, Moscow, Kenya, and so forth), you can change the time zone by using a + and the appropriate time zone:

 phpbook=> SELECT '12:16:32.43+03'::time with time zone;    timetz -------------  12:16:32+03 (1 row) 

If the same string is passed to the database as time without time zone, an error will be displayed:

 phpbook=> SELECT '12:16:32.43+03'::time without time zone; ERROR:  Bad time external representation '12:16:32.43+03' 

In the preceding examples, you saw that the time zone is defined using a + and the offset of hours relative to Greenwich. However, the time zone can be passed to PostgreSQL differently as well; let's take a look at the next example:

 phpbook=> SELECT '12:16 GMT'::time with time zone;    timetz -------------  12:16:00+00 (1 row) 

One important point is the way EST is treated. Normally EST is short for Eastern Standard Time (Romeo, GMT 5). Let's take a look at an example:

 phpbook=> SELECT '12:16 EST'::time with time zone;    timetz -------------  12:16:00-05 (1 row) 

If you want EST to be treated as Australian time zone, you have to set a runtime parameter:

 phpbook=> SET australian_timezones=true; SET VARIABLE 

This flag can be changed in postgresql.conf. If you just want to use this setting temporarily, you can use the SET command. After setting the variable, run SELECT again:

 phpbook=> SELECT '12:16 EST'::time with time zone;    timetz -------------  12:16:00+10 (1 row) 

As you see, the result differs from the one you saw before because EST has been treated as an Australian time zone.

To compute the current time, now can be used just as you saw when dealing with date:

 phpbook=> SELECT 'now'::time with time zone;    timetz -------------  09:57:02+00 (1 row) 

To modify the time zone you are operating in, you can use SET again. In the following example, the time zone is set to EST:

 phpbook=> SET TIME ZONE 'EST'; SET VARIABLE 

Let's run now again and see that (nearly) the same data is returned, but this time a different time zone is used:

 phpbook=> SELECT 'now'::time with time zone;    timetz -------------  04:57:10-05 (1 row) 

17.1.1.4 timestamp [ with time zone ]

The data type timestamp can be used in two ways. One way is to use timestamp with time zone. This way the values accepted by PostgreSQL range from 1903 AD to 2037 AD. The precision is 1 microsecond. If you don't have to store time zones, the range of valid data is from 4713 BC to AD 1465001. Again, the precision is 1 microsecond. If you don't have to work with time zones, the range of values is higher because internally no data is wasted for storing the time zone.

In general, timestamps are a combination of date and time if you keep this in mind, you will find out easily how timestamps can be defined. Let's see how the data types can be used:

 phpbook=> SELECT '24.12.2001'::timestamp;       timestamptz ------------------------  2001-12-24 00:00:00+01 (1 row) 

In this example the German way of displaying a date is used. The format is the same as when working with dates. If no time is defined, hours, minutes, and seconds are set to zero.

If a valid time is defined, it will be displayed in the result. In addition, the time zone is mentioned:

 phpbook=> SELECT '24.12.2001 16:34:09'::timestamp;       timestamptz ------------------------  2001-12-24 16:34:09+01 (1 row) 

If you don't have to work with time zones, you can turn them off explicitly:

 phpbook=> SELECT '24.12.2001 16:34:09'::timestamp without time zone;       timestamp ---------------------  2001-12-24 16:34:09 (1 row) 

Just as when working with time, you can define a time zone using the abbreviation of the time zone you want to use. The next example shows how Japanese time can be used:

 phpbook=> SELECT '24.12.2001 16:34:09 JST'::timestamp;       timestamptz ------------------------  2001-12-24 08:34:09+01 (1 row) 

As you can see, there are nine hours of offset relative to GMT.

In addition to the German style, dates can be defined differently as well:

 phpbook=> SELECT '2001/12/24 16:34:09 JST'::timestamp;       timestamptz ------------------------  2001-12-24 08:34:09+01 (1 row) 

To define a month, strings can be used just as you saw when working with dates:

 phpbook=> SELECT 'November 10 2002 16:34:09'::timestamp;       timestamptz ------------------------  2002-11-10 16:34:09+01 (1 row) 

The year need not be defined immediately after the month. It is also possible to define the year at the end of the string:

 phpbook=> SELECT 'November 10 16:34:09 2002'::timestamp;       timestamptz ------------------------  2002-11-10 16:34:09+01 (1 row) 

Of course, it is also possible to pass the data to the database the same way it is returned by PostgreSQL:

 phpbook=> SELECT '2002-11-10 16:34:09+01'::timestamp;       timestamptz ------------------------  2002-11-10 16:34:09+01 (1 row) 

17.1.1.5 Time Zones

Up to now, you have seen how time zones can be used when working with SQL. In this section you will get an overview of the time zones available. Table 17.3 shows the international time zones.

Table 17.3. International Time Zones

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*		Australian 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	M±			Line Islands: Kiritimati
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

17.1.2 Functions

PostgreSQL provides a set of functions for working with dates. In many cases, it is easier to use these functions than to work with PHP functions. Whether it is better to execute a function using PostgreSQL or PHP may vary, depending on how much load your database server can stand. In this section you will take a closer look at PostgreSQL's onboard functions and see how they can be used efficiently.

One function that is widely used is the now function, which computes the current timestamp:

 phpbook=> SELECT now();               now -------------------------------  2002-01-10 23:36:21.557874+01 (1 row) 

As you can see, the current time is returned precisely and even the time zone is mentioned.

In some cases it might be useful to extract parts of the result. You can use a function called date_part. The first parameter of the function defines the component you want to retrieve. The second parameter must contain the data you want to process. Take a look at the next two examples:

 phpbook=> SELECT date_part('year', now());  date_part -----------       2002 (1 row) phpbook=> SELECT date_part('minute', now());  date_part -----------         11 (1 row) 

First the year has been extracted. In the second example the minute has been extracted from the result. This function is a comfortable one because you need not write a function for parsing the result on an application level.

Sometimes it is not necessary to extract a component from a date, but it is necessary to cut off various parts of the data. This can be done by using the date_trunc function. The first parameter accepted by the functions defines the component having the maximum precision you want to cut off. In the next two examples you can see how this function works:

 phpbook=> SELECT now(), date_trunc('month', now());               now              |       date_trunc -------------------------------+------------------------  2002-01-12 00:12:30.346589+01 | 2002-01-01 00:00:00+01 (1 row) phpbook=> SELECT now(), date_trunc('minute', now());               now              |       date_trunc -------------------------------+------------------------  2002-01-12 00:12:44.210323+01 | 2002-01-12 00:12:00+01 (1 row) 

First, all digits from month on are set to the lowest value. In the second example you can see that all digits from minute on are set to zero. The hour is not modified any more.

To extract data from a timestamp, PostgreSQL provides an additional method:

 phpbook=> SELECT extract(hour from timestamp '2001-1-12 16:32');  date_part -----------         16 (1 row) 

In this example, the hour is extracted from the timestamp passed to the function. As you might have expected, the result is 16.

17.1.3 Simple Calculations

Now that you have seen which data types and functions are provided by PostgreSQL, you will take a look at some simple calculations. As you have already seen, PostgreSQL provides a powerful interface for working with dates and time. In this section you will see that performing simple computations can also be done easily and reliably.

Let's get started with an example:

 phpbook=# SELECT now() - '2001/1/1';      ?column? -------------------  376 days 13:18:08 (1 row) 

A simple subtraction has been performed. The current date is about 376 days after January 1, 2001. If you want to add two times, you will get an error:

 phpbook=# SELECT now() + '2001/1/1'; ERROR:  Bad interval external representation '2001/1/1' 

The addition of two times is not defined. If you think about it logically, you will find that PostgreSQL's behavior seems to be clear.

To perform additions, you can use the data type interval. In the next example you can see how 30 days can be added to the result of the now function:

 phpbook=# SELECT now(), now() + '30 days';           now           |        ?column? ------------------------+------------------------  2002-01-12 13:20:01+01 | 2002-02-11 13:20:01+01 (1 row) 

In this example it was not necessary to cast the string to interval explicitly. However, in some cases casting has to be done explicitly, as you can see in the next listing:

 phpbook=# SELECT '30 days'::interval + '30 days'::interval;  ?column? ----------  60 days (1 row) 

The two components are cast to interval, so the addition can be performed easily. The result is not surprising. Because a month does not have a fixed length, the result is not displayed as months and days.

The same thing can be seen in the next example:

 phpbook=# SELECT '1 month'::interval + '32 days'::interval;    ?column? ---------------  1 mon 32 days (1 row) 

However, in the case of hours the situation is different because the number of minutes in an hour is exactly 60, so 120 minutes make exactly two hours:

 phpbook=# SELECT '1 day'::interval + '120 minutes'::interval;   ?column? -------------  1 day 02:00 (1 row) 

Another important point is that PostgreSQL does not distinguish between singular and plural words. In the next example you can see that PostgreSQL automatically makes plural words out of singular words:

 phpbook=# SELECT '2 day'::interval + '120 minutes'::interval;    ?column? --------------  2 days 02:00 (1 row) 

Multiplications and divisions can be performed easily as well:

 phpbook=# SELECT '2 hours'::interval / 10;  ?column? ----------  00:12 (1 row) phpbook=# SELECT '2 hours'::interval * 12;  ?column? ----------  1 day (1 row) 

In the first example, 2 hours are divided by 10. The result is 12 minutes, which is correct. In the second example you can see that two hours are multiplied by 12. The result is 1 day.