More dramatically, you may subtract one integer (INT - INT) from another, but you may not subtract one string from another (VARCHAR - VARCHAR), let alone subtract a string from an integer (INT - VARCHAR). Thus the division of two integers ( INT / INT ) does not function in the same way as the division of two float values (FLOAT / FLOAT). In the ANSI SQL world, operators (such as + - * % || !) are defined only in the context of the data types being operated upon. There are plenty of other non-compliant databases on the market.) I use MS SQL Server as an example of a non-standards-compliant database because I am a certified MS SQL Server admin and know its problems quite well. (BTW, I am not on an anti-Microsoft tirade here. If you are already educated, you'll want to skip down to "Working with DATETIME, DATE, and INTERVAL values". This has allowed me to write calendaring applications in PostgreSQL that would have been considerably more difficult on other platforms.īefore we get down to the nuts-and-bolts, I need to explain a few things to the many who have come to us from database applications which are less ANSI 92 SQL compliant than PostgreSQL (particularly Microsoft SQL Server, SyBase and Microsoft Access). One of PostgreSQL's joys is a robust support of a variety of date and time data types and their associated operators. This advice is provided with no warranty whatsoever, including any warranty of fitness for a particular purpose. Permission granted to use in any public forum for which no fee is charged if this copyright notice appears in the document, or alternately in any published for-fee work if 1% or more of the proceeds of such work are donated or paid to benefit PostgreSQL development. 4.7 What if I want to get the month as an integer out of a date?Ĭopyright 2001 Josh Berkus ( ).4.6 I need to display a DATE as text, or convert text into a DATE or INTERVAL.4.5 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. ![]() 4.4.5 DATE/INTEGER cannot figure out the varying lengths of months and years.4.4.4 As with TIMESTAMP, you may NOT perform Addition, Multiplication, Division, or other operations with two DATES.4.4.3 Because the difference of two DATES is an INTEGER, this difference may be added, subtracted, divided, multiplied, or even modulo (%).4.4.2 You may add or subtract an INTEGER to a DATE to produce another DATE.The difference between two DATES is always an INTEGER, representing the number of DAYS difference Many larger INTERVAL values, like the calendar values they reflect, are not constant in length when expressed in smaller INTERVAL values You may NOT (ever) perform Addition, Multiplication, or Division operations with two TIMESTAMPS ![]() Multiplication and division of INTERVALS is under development and discussion at this time You may add or subtract an INTERVAL to a TIMESTAMP to produce another TIMESTAMP The difference between two TIMESTAMPs is always an INTERVAL
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |