반응형
Complete docs on date/time data types may be found at : http://www.postgresql.org/idocs/index.php?datatype-datetime.html. I will not attempt to reproduce them here. Instead, I will simply try to explain to the beginner what you need to know to actually work with dates, times, and intervals.
DATETIME or TIMESTAMP : Structured "real" date and time values, containing year, month, day, hour, minute, second and millisecond for all useful date & time values (4713 BC to over 100,000 AD).
DATE : Simplified integer-based representation of a date defining only year, month, and day.
INTERVAL : Structured value showing a period of time, including any/all of years, months, weeks, days, hours, minutes, seconds, and milliseconds. "1 day", "42 minutes 10 seconds", and "2 years" are all INTERVAL values.
Q. What about TIMESTAMP WITH TIME ZONE?
A. An important topic, that I don't want to get into here. Eventually someone will document this. Suffice it to say that all TIMESTAMP values carry TIMEZONE data as well which you may safely ignore if you don't need to handle different time zones.
Q. Which do I want to use: DATE or TIMESTAMP? I don't need minutes or hours in my value.
A. That depends. DATE is easier to work with for arithmetic (e.g. something reoccurring at a random interval of days), takes less storage space, and doesn't trail "00:00:00" strings you don't need when printed. However, TIMESTAMP is far better for real calendar calculations (e.g. something that happens on the 15th of each month or the 2nd Thursday of leap years). More below.
Now, to work with TIMESTAMP and INTERVAL, you need to understand these few simple rules :
The difference between two TIMESTAMPs is always an INTERVAL :
TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' = INTERVAL '19 days'
You may add or subtract an INTERVAL to a TIMESTAMP to produce another TIMESTAMP :
TIMESTAMP '1999-12-11' + INTERVAL '19 days' = TIMESTAMP '1999-12-30'
You may add or subtract two INTERVALS :
INTERVAL '1 month' + INTERVAL '1 month 3 days' = INTERVAL '2 months 3 days'
Multiplication and division of INTERVALS is under development and discussion at this time; it is suggested that you avoid it until implementation is complete or you may get unexpected results.
You may NOT (ever) perform Addition, Multiplication, or Division operations with two TIMESTAMPS:
TIMESTAMP '2001-03-24' + TIMESTAMP '2001-10-01' = OPERATION ERROR
Finally, the most important rule to keep in mind :
While minutes and hours are relatively constant, many larger INTERVAL values, like the calendar values they reflect, are *not* constant in length when expressed in smaller INTERVAL values. For example :
TIMESTAMP '2001-07-02' + INTERVAL '1 month' = TIMESTAMP '2001-08-02'
TIMESTAMP '2001-07-02' + INTERVAL '31 days' = TIMESTAMP '2001-08-02'
but :
TIMESTAMP '2001-02-02' + INTERVAL '1 month' = TIMESTAMP '2001-03-02'
TIMESTAMP '2001-02-02' + INTERVAL '31 days' = TIMESTAMP '2001-03-05'
This makes the TIMESTAMP/INTERVAL combination ideal, for example, for scheduling an event which must reoccur every month on the 8th regardless of the length of the month, but problematic if you are trying to figure out the number of days in the last 3.5 months. Keep it in mind!
The DATE datatype, however, is simpler to deal with if less powerful.
Here's your rules:
The difference between two DATES is always an INTEGER, representing the number of DAYS difference:
DATE '1999-12-30' - DATE '1999-12-11' = INTEGER 19
You may add or subtract an INTEGER to a DATE to produce another DATE:
DATE '1999-12-11' + INTEGER 19 = DATE '1999-12-30'
Because the difference of two DATES is an INTEGER, this difference may be added, subtracted, divided, multiplied, or even modulo (%) to your heart's
content.
As with TIMESTAMP, you may NOT perform Addition, Multiplication, Division, or other operations with two DATES.
Because DATE differences are always calculated as whole numbers of days, DATE/INTEGER cannot figure out the varying lengths of months and years. Thus, you cannot use DATE/INTEGER to schedule something for the 5th of every month without some very fancy length-of-month calculating on the fly. This makes DATE ideal for calendar applications involving a lot of calculating based on numbers of days (e.g. "For how many 14-day periods has employee "x" been employed?") but poor for actual calendaring apps. Keep it in mind.
Q. All that is terrific, but I'm porting an app from MS SQL Server, and I need to support the DATEDIFF and DATEADD functions so that my stored views will work under PostgreSQL. What do I do?
A. Proceed to PostgreSQL TechDocs (http://techdocs.postgresql.org). There are many porting resources there, and I'd be surprised if someone hasn't already re-created these functions under PostgreSQL.
Q. I need to display a DATE as text, or convert text into a DATE or INTERVAL.
A. You want the to_date(), to_char(), and interval() functions. See "functions and operators" in the PostgreSQL docs: http://www.postgresql.org/idocs/index.php?functions.html
Q. What if I want to get the month as an integer out of a date?
A. You want the extract() function. This function also works to give you other numeric intervals from a timestamp, including the Unix system datetime (e.g. EXTRACT ( epoch from some_date ))
DATETIME or TIMESTAMP : Structured "real" date and time values, containing year, month, day, hour, minute, second and millisecond for all useful date & time values (4713 BC to over 100,000 AD).
DATE : Simplified integer-based representation of a date defining only year, month, and day.
INTERVAL : Structured value showing a period of time, including any/all of years, months, weeks, days, hours, minutes, seconds, and milliseconds. "1 day", "42 minutes 10 seconds", and "2 years" are all INTERVAL values.
Q. What about TIMESTAMP WITH TIME ZONE?
A. An important topic, that I don't want to get into here. Eventually someone will document this. Suffice it to say that all TIMESTAMP values carry TIMEZONE data as well which you may safely ignore if you don't need to handle different time zones.
Q. Which do I want to use: DATE or TIMESTAMP? I don't need minutes or hours in my value.
A. That depends. DATE is easier to work with for arithmetic (e.g. something reoccurring at a random interval of days), takes less storage space, and doesn't trail "00:00:00" strings you don't need when printed. However, TIMESTAMP is far better for real calendar calculations (e.g. something that happens on the 15th of each month or the 2nd Thursday of leap years). More below.
Now, to work with TIMESTAMP and INTERVAL, you need to understand these few simple rules :
The difference between two TIMESTAMPs is always an INTERVAL :
TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' = INTERVAL '19 days'
You may add or subtract an INTERVAL to a TIMESTAMP to produce another TIMESTAMP :
TIMESTAMP '1999-12-11' + INTERVAL '19 days' = TIMESTAMP '1999-12-30'
You may add or subtract two INTERVALS :
INTERVAL '1 month' + INTERVAL '1 month 3 days' = INTERVAL '2 months 3 days'
Multiplication and division of INTERVALS is under development and discussion at this time; it is suggested that you avoid it until implementation is complete or you may get unexpected results.
You may NOT (ever) perform Addition, Multiplication, or Division operations with two TIMESTAMPS:
TIMESTAMP '2001-03-24' + TIMESTAMP '2001-10-01' = OPERATION ERROR
Finally, the most important rule to keep in mind :
While minutes and hours are relatively constant, many larger INTERVAL values, like the calendar values they reflect, are *not* constant in length when expressed in smaller INTERVAL values. For example :
TIMESTAMP '2001-07-02' + INTERVAL '1 month' = TIMESTAMP '2001-08-02'
TIMESTAMP '2001-07-02' + INTERVAL '31 days' = TIMESTAMP '2001-08-02'
but :
TIMESTAMP '2001-02-02' + INTERVAL '1 month' = TIMESTAMP '2001-03-02'
TIMESTAMP '2001-02-02' + INTERVAL '31 days' = TIMESTAMP '2001-03-05'
This makes the TIMESTAMP/INTERVAL combination ideal, for example, for scheduling an event which must reoccur every month on the 8th regardless of the length of the month, but problematic if you are trying to figure out the number of days in the last 3.5 months. Keep it in mind!
The DATE datatype, however, is simpler to deal with if less powerful.
Here's your rules:
The difference between two DATES is always an INTEGER, representing the number of DAYS difference:
DATE '1999-12-30' - DATE '1999-12-11' = INTEGER 19
You may add or subtract an INTEGER to a DATE to produce another DATE:
DATE '1999-12-11' + INTEGER 19 = DATE '1999-12-30'
Because the difference of two DATES is an INTEGER, this difference may be added, subtracted, divided, multiplied, or even modulo (%) to your heart's
content.
As with TIMESTAMP, you may NOT perform Addition, Multiplication, Division, or other operations with two DATES.
Because DATE differences are always calculated as whole numbers of days, DATE/INTEGER cannot figure out the varying lengths of months and years. Thus, you cannot use DATE/INTEGER to schedule something for the 5th of every month without some very fancy length-of-month calculating on the fly. This makes DATE ideal for calendar applications involving a lot of calculating based on numbers of days (e.g. "For how many 14-day periods has employee "x" been employed?") but poor for actual calendaring apps. Keep it in mind.
Q. All that is terrific, but I'm porting an app from MS SQL Server, and I need to support the DATEDIFF and DATEADD functions so that my stored views will work under PostgreSQL. What do I do?
A. Proceed to PostgreSQL TechDocs (http://techdocs.postgresql.org). There are many porting resources there, and I'd be surprised if someone hasn't already re-created these functions under PostgreSQL.
Q. I need to display a DATE as text, or convert text into a DATE or INTERVAL.
A. You want the to_date(), to_char(), and interval() functions. See "functions and operators" in the PostgreSQL docs: http://www.postgresql.org/idocs/index.php?functions.html
Q. What if I want to get the month as an integer out of a date?
A. You want the extract() function. This function also works to give you other numeric intervals from a timestamp, including the Unix system datetime (e.g. EXTRACT ( epoch from some_date ))
반응형