Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Date and Time Types

Date and time types are a convenient way to store date and time related data in a uniform SQL data structure, without having to worry about the conventions involved with storage (e.g., if you were to try to store such information in a character data type). PostgreSQL uses Julian dates for all date and time calculations. Julian date representation is the commonly used January through December calendar that you are most likely familiar with. By fixing the length of a year at about 365.24 days, Julian dates can correctly calculate any date after 4713 BC, as well as far into the future.

PostgreSQL supports all of the SQL92-defined date and time types shown in Table 3-14, as well as some PostgreSQL-specific extensions to help with SQL92's timezone limitations.

Table 3-14. Date and time types

Name

Storage

Description

Range

date

4 bytes

A calendar date (year, month, and day)

4713 BC to 32767 AD

time

4 bytes

The time of day only, without time zone information

00:00:00.00 to 23:59:59.99

time with time zone

4 bytes

The time of day only, including a time zone

00:00:00.00+12 to 23:59:59.99-12

timestamp (includes time zone)

8 bytes

Both the calendar date and time, with time zone information

1903 AD to 2037 AD

interval

12 bytes

A general time span interval

–1780000000 years to 17800000 years

Backward compatibility

To ensure compatibility with earlier versions of PostgreSQL, the developers have continued to provide the older datetime and timespan data types. The datetime type is now equivalent to timestamp, while the timespan is now equivalent to the interval types.

Other date/time data types include abstime and reltime, which are lower precision types. However, these types are internal to PostgreSQL, and any or all of these types may disappear in a future release. It is advised therefore to design new applications with the SQL-compliant data types in mind, and to convert older applications from any of these data types as soon as is possible.

Date conventions

Date input can be accepted by PostgreSQL in many common formats, including the ISO-8601 format, the traditional SQL format, the original PostgreSQL format, and more. Table 3-15 lists several of these date formats.

These formats are relevant to the date and the timestamp data types.

Table 3-15. Valid date formats

Format Example

Description

July 1, 2001

Named month, day and year

Sunday July 1, 2001

Named day, named month, day and year

July 15, 01 BC

Named month, day and year before the Common Era

2001-07-01

Standard ISO-8601 format: numeric year, month and day

20010715

ISO-8601: formatted numerically as complete year, month, day

010715

ISO-8601: formatted numerically as 2-digit year, month, day

7/01/2001

Non-European (U.S.) format: numeric month, day and year

1/7/2001

European format: numeric day, month and year

2001.182

Numeric format, with complete year, and sequential day of the year

When specifying a named month in a date value to PostgreSQL, you may either type the complete month name, or choose from a set of defined abbreviations for each month. These abbreviations are listed in Table 3-16.

Table 3-16. Month abbreviations

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

Similarly, Table 3-17 lists PostgreSQL-recognized abbreviations for weekday names.

Table 3-17. Day of the week abbreviations

Day

Abbreviation

Sunday

Sun

Monday

Mon

Tuesday

Tue, Tues

Wednesday

Wed, Weds

Thursday

Thu, Thur, Thurs

Friday

Fri

Saturday

Sat

Despite the wide variety of ways in which PostgreSQL can interpret date values, the values are always stored uniformally, and will be returned in a consistent format. As such, you have a variety of methods available to you to customize the default behavior with which date and time values are returned to you.

Note: While date values can always be formatted during selection via several formatting functions (e.g., to_char()), it is more efficient to configure your defaults as close to the most commonly used conventions as you can before having to resort to manual type conversion and text formatting.

To set the general date/time output format, the SET command can be applied to the run-time variable DATESTYLE. This variable may be set to one of four available general styles shown in Table 3-18.

Table 3-18. Date output formats

General format

Description

Example

ISO

ISO-8601 standard

2001-06-25 12:24:00-07

SQL

Traditional SQL style

06/25/2001 12:24:00.00 PDT

Postgres

Original PostgreSQL style

Mon 25 Jun 12:24:00 2001 PDT

German

Regional style for Germany

25.06.2001 12:24:00.00 PDT

As an example, you can use the following SQL statement to set the date style to SQL :

booktown=# 
SET DATESTYLE TO SQL;

SET VARIABLE

If you perform a SELECT current_timestamp query after setting this variable, PostgreSQL should return the current time using the ISO format as instructed:

booktown=# 
SELECT current_timestamp;

         timestamp
----------------------------
 08/10/2001 13:25:55.00 PDT
(1 row)

The SHOW command can be used to display the current value of the DATESTYLE variable while PostgreSQL is running.

booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is SQL with US (NonEuropean) conventions
SHOW VARIABLE

In addition to these general formats, PostgreSQL's date output format has two other variants which further describe how to display the date, shown in Table 3-19: European and non-European (U.S.). These determine whether the format is day followed by month, or vice versa. This variation can be applied on top of the previous four general formats with the same syntax to SET DATESTYLE and will not modify your chosen format except for the arrangement of the month and day.

Table 3-19. Extended date output formats

Month/day format

Description

Example

European

day/month/year

12/07/2001 17:34:50.00 MET

U.S., or Non-European

month/day/year

07/12/2001 17:34:50.0 PST

Furthermore, you may set both the general format and day/month convention by supplying both variables to the SET command, comma delimited. The order of these variables is not important to the SET command as long as the variables are not mutually exclusive (e.g., SQL and ISO ), as shown in Example 3-26.

Example 3-26. Setting date formats

booktown=# 
SET DATESTYLE TO ISO,US;

SET VARIABLE
booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
SHOW VARIABLE
booktown=# 
SET DATESTYLE TO NONEUROPEAN, GERMAN;

SET VARIABLE
booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is German with European conventions
SHOW VARIABLE

If you do not specify a month/day format, a reasonable default will usually be chosen (e.g., European is the default for the German regional format).

While SET DATESTYLE is a convenient way to set the output format, it is important to note that this is a run-time variable , which means that it exists only for the lifespan of your connected session. There are two methods available that allow you to provide a default value for the DATESTYLE variable, which lets you avoid explicitly setting the variable for each new session you begin:

  • You may change the PGDATESTYLE environment variable on the server running postmaster . For example, with the bash shell, you could add the export PGDATESTYLE="SQL US" line to the postgres user's .bash_ profile file. When the postgres user starts postmaster , the PGDATESTYLE variable will be read and applied globally to all date and time formatting performed by PostgreSQL.

  • You may change the PGDATESTYLE environment variable used by a client application (assuming it was written with the libpq library) on its session start-up, if you wish the client rather than the server to configure the output. For example, setting the PGDATESTYLE variable at a bash prompt with the export command before starting psql sets the format for psql to use.

Time conventions

Time values, like date values, may be entered in to a table in a number of ways. Commonly used formats are listed in Table 3-20. These apply to values of type time and time with time zone.

Table 3-20. Valid time formats

Format example

Description

01:24

ISO-8601, detailed to minutes

01:24 AM

Equivalent to 01:24 (the "AM" attached is for readability only, and does not affect the value)

01:24 PM

Equivalent to 13:24 (the hour must be less-than or equal to 12 to use "PM")

13:24

24-hour time, equivalent to 01:24 PM

01:24:11

ISO-8601, detailed to seconds

01:24:11.112

ISO-8601, detailed to microseconds

012411

ISO-8601, detailed to seconds, formatted numerically

In addition to these formats, PostgreSQL allows for further description of a time value which is defined as time with time zone by supporting extra time zone parameters following the time value. The supported formats are illustrated in Table 3-21.

Table 3-21. Valid time zone formats

Format example

Description

01:24:11-7

ISO-8601, 7 hours behind GMT

01:24:11-07:00

ISO-8601, 7 hours, zero minutes behind GMT

01:24:11-0700

ISO-8601, 7 hours, zero minutes behind GMT

01:24:11 PST

ISO-8601, Pacific Standard Time (7 hours behind GMT)

Note: PostgreSQL supports the use of all ISO standard time zone abbreviations.

The time with time zone data type is mainly supported by PostgreSQL to adhere to existing SQL standards and for portability with other database management systems. If you need to work with time zones, it is recommended that you use the timestamp data type discussed in the Section called Timestamps ." This is primarily because of the fact that, due to daylight savings, time zones cannot always be meaningfully interpreted without an associated date.

Internally, PostgreSQL keeps track of all time zone information as a numeric offset of GMT (Greenwich Mean Time), which is also known as UTC (Universal Coordinated Time). By default, PostgreSQL's time display will use the time zone that your server's operating system is configured for. If you wish the time value to operate under a different time zone, there are four ways in which you can modify the output:

Set the TZ environment variable on the server

This variable is found by the backend server as the default time zone when the postmaster starts up. It can be set, for example, in the postgres user's .bash_ profile file with a bash export TZ='zone' command.

Set the PGTZ environment variable on the client

If the PGTZ environment variable is set, it can be read by any client written with libpq and interpreted as the client's default time zone.

Use the SET TIMEZONE TO SQL statement

This SQL command sets the time zone for the session to zone (e.g., SET TIMEZONE TO UTC)

Use the AT TIME ZONE SQL clause

This SQL92 clause can be used to specify zone as a text time zone (e.g., PST  ) or as an interval (e.g., interval('—07:00')). This clause may be applied in the middle of a SQL statement following a value which contains a timestamp (e.g., SELECT my_timestamp AT TIME ZONE 'PST').

Note: Most systems will default to GMT when a time zone variable is set to an invalid time zone.

Additionally, if the compiler option USE_AUSTRALIAN_RULES was set when PostgreSQL was built, the EST time zone will refer to Australian Eastern Standard Time (with an offset of +10:00 hours from GMT) rather than U.S. Eastern Standard Time.

Timestamps

The PostgreSQL timestamp combines the functionality of the PostgreSQL date and time types into a single data type. The syntax of a timestamp value consists of a valid date format, followed by at least one whitespace character, and a valid time format. It can be followed optionally by a time zone value, if specified.

Combinations of all date and time formats listed in Table 3-15 and Table 3-20 are each supported in this fashion. Table 3-22 illustrates some examples of valid timestamp input.

Table 3-22. Some valid timestamp formats

Format Example

Description

1980-06-25 11:11-7

ISO-8601 date format, detailed to minutes, and PST time zone

25/06/1980 12:24:11.112

European date format, detailed to microseconds

06/25/1980 23:11

U.S. date format, detailed to minutes in 24-hour time

25.06.1980 23:11:12 PM

German regional date format, detailed to seconds, and PM attached

Warning

While PostgreSQL supports the syntax of creating a column or value with the type timestamp without time zone, as of PostgreSQL 7.1.2 the resultant data type still contains a time zone.

Intervals

The SQL92 standard specifies a data typed called an interval , which represents a fixed span of time. By itself, an interval represents only a quantity of time , and does not begin or end at any set date or time. These intervals can be useful when applied to date and time values to calculate a new date or time, either by subtracting or adding the quantity. They can also be handy for quickly determining the precise interval between two date or time values. This can be achieved by subtracting date values, time values or timestamps from one another.

The two syntax variations below can specify an interval within PostgreSQL:

  
qty
 
unit
  [ ago ]
  
qty1
 
unit
 [, 
qty2
 
unit2
 ... ] [ ago ]

Where:

qty

Specifies the quantity of your interval, which may be any whole integer, or floating-point number in the case of microseconds. The literal meaning of this number is qualified by the subsequent unit .

unit

Qualifies the qty provided. The unit may be any one of the following keywords: second, minute, hour, day, week, month, year, decade, century, millennium. It can also be an abbreviation (as short as you want, as long as it cannot be confused with another keyword) or plurals of the previously mentioned units.

ago

The optional ago keyword of the interval determines whether or not you are describing a period of time before the associated time, rather than after. You can think of it as a negative sign for date and time types.

Example 3-27 shows functional syntax for date and interval values being meaningfully combined. You can see that subtracting an inverted time interval (e.g., one with the term ago) is functionally identical to adding a normal interval. This can be thought of as similar to the effect of adding negative numbers to integer values.

Example 3-27. Interpreting interval formats

booktown=# 
SELECT date('1980-06-25');

    date
------------
 1980-06-25
(1 row)

booktown=# 
SELECT interval('21 years 8 days');

    interval
-----------------
 21 years 8 days
(1 row)

booktown=# 
SELECT date('1980-06-25') + interval('21 years 8 days') 

booktown-# 
AS spanned_date;

      spanned_date
------------------------
 2001-07-03 00:00:00-07
(1 row)

booktown=# 
SELECT date('1980-06-25') - interval('21 years 8 days ago') 

booktown-# 
AS twice_inverted_interval_date;

 twice_inverted_interval_date
------------------------------
 2001-07-03 00:00:00-07
(1 row)

Built-in date and time constants

PostgreSQL supports many special constants for use when referencing dates and times. These constants represent common date/time values, such as now , tomorrow , and yesterday . The predefined date and time constants supported by PostgreSQL are listed in Table 3-23.

PostgreSQL also provides three built-in functions for retrieving the current time, date, and timestamp. These are aptly named current_date, current_time, and current_timestamp.

Table 3-23. Date and time constants

Constant

Description

current

The current transaction time, deferred. Unlike a now , current is not a timestamp; it represents the current system time and can be used to reference whatever that time may be.

epoch

1970-01-01 00:00:00+00 (UNIX's "Birthday")

infinity

An abstract constant later than all other valid dates and times

-infinity

An abstract constant earlier than all other valid dates and times

now

The current transaction timestamp

today

Midnight, on the current day

tomorrow

Midnight, on the day after the current day

yesterday

Midnight on the day before the current day

The now and current timestamp constants may seem to be identical, looking solely at their names. They are, however, very different in terms of storing them in a table. The now constant is translated into the timestamp of the system time at the execution of whichever command referenced it (e.g., the time of insertion, it now had been referenced in an INSERT statement). In contrast, the current constant, as it is a deferred identifier, will actually appear as the phrase current in the database. From there, it can be translated (e.g., via the to_char() function) to the timestamp associated with the transaction time of any query which requests that value .

In other words, current will always tell you the "current" time when queried, regardless of when it was stored to the table. The current constant can be used in special situations, such as process tracking, where you may need to calculate the difference between a timestamp made with now and the current date and time to find the total time the process has been running. Example 3-28 demonstrates using the now and current constants to create a log of tasks. First, a table is created to house the task's name, its start date and time, and its finished date and time. Two tasks are then added to the table, using the now constant to set the start date and current to set the completed date. The reason this is done is to show that both of these tasks are uncompleted. If a task were to be completed, the table could be updated to show a now timestamp for that task's timefinished column.

Note: The use of time/date constants requires the use of single-quotes around their respective names. See Example 3-28 for a valid representation of single-quoted time/date constants.

Example 3-28. Using the current and now constants

booktown=# 
CREATE TABLE tasklog

booktown=# 
  (taskname char(15),

booktown=# 
   timebegun timestamp,

booktown=# 
   timefinished timestamp);

CREATE
booktown=# 
INSERT INTO tasklog VALUES

booktown=# 
   ('delivery', 'now', 'current');

INSERT 169936 1
booktown=# 
INSERT INTO tasklog VALUES

booktown=# 
   ('remodeling', 'now', 'current');

INSERT 169937 1
booktown=# 
SELECT taskname, timefinished - timebegun AS timespent FROM tasklog;

    taskname     | timespent
-----------------+-----------
 delivery        | 00:15:32
 remodeling      | 00:04:42
(2 rows)

Therefore, you generally want to use now when storing a transaction timestamp in a table, or even the current_timestamp function, which is equivalent to the output of now . Example 3-29 shows how this could be a potentially disastrous SQL design issue if not properly understood. It shows a pair of INSERT statements; one which uses now , another which uses current. If you watch the first row returned from the two queries (the row with a current timestamp), you'll notice it changes in each query to show the updated system time, while the second row remains the same (this is he the row in which now was used).

Example 3-29. Comparing now to current

booktown=# 
INSERT INTO shipments (customer_id, isbn, ship_date) 

booktown-# 
       VALUES (1, '039480001X', 'current');

INSERT 3391221 1
booktown=# 
INSERT INTO shipments (customer_id, isbn, ship_date) 

booktown-# 
       VALUES (2, '0394800753', 'now');

INSERT 3391222 1
booktown=# 
SELECT isbn, ship_date FROM shipments;

    isbn    |       ship_date
------------+------------------------
 039480001X | current
 0394800753 | 2001-08-10 18:17:49-07
(2 rows)

booktown=# 
SELECT isbn, 

booktown-# 
       to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') 

booktown-# 
       AS value

booktown-# 
  FROM shipments;

    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:21:22
 0394800753 | 2001-08-10 18:17:49
(2 rows)

booktown=# 
SELECT isbn, to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') AS value

booktown-# 
  FROM shipments;

    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:22:35
 0394800753 | 2001-08-10 18:17:49
(2 rows)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire