![]() ![]() The database can handle the implicit conversions for you, because they have been simplified. Thus, in Microsoft Transact-SQL, you may add a DOUBLE and an INTEGER, or even append an INTEGER directly to a string in some cases. Some database developers, in a rush to get their products to market, saw the above "user-unfriendly" behaviour and cut it out of the system by defining all operators to work in a context-insensitive way. ![]() Further, if you want to define your own data types, you must spend the hours necessary to define all possible operators for them as well. Even more so, appending an integer to the end of a string requires a type conversion function (to_char(INT, '00000')). For example, try adding a FLOAT and a NUMERIC value you will get an error until you help out the database by defining them both as FLOAT or both as NUMERIC (CAST(FLOAT AS NUMERIC) + NUMERIC). Frequently you must CAST two values to the same data type in order to work with them. This fundamental rule has a number of tedious consequences. In the absence of a predefined context, the operator does not function at all and you get an error message. The subtraction operator (-) in these two operations, while it looks the same, is in fact not the same owing to a different datatype context. 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 4.3 Which do I want to use: DATE or TIMESTAMP? I don't need minutes or hours in my value.4.2 What about TIMESTAMP WITH TIME ZONE?.4 WORKING with DATETIME, DATE, and INTERVAL VALUES. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |