Fwd: Re: [GENERAL] Interval 1 month is equals to interval 30 days - WHY?
This was supposed to go to the list. Sorry.
-- Forwarded message --
From: Michael Trausch m...@trausch.us
Date: Aug 8, 2012 10:12 AM
Subject: Re: [GENERAL] Interval 1 month is equals to interval 30 days -
WHY?
To: Albe Laurenz laurenz.a...@wien.gv.at
There is root in accounting for this type of view of the interval. In
accounting, a month is considered to have 30 days or 4.25 weeks, and a year
is considered to have 360 days. The reason for this is that both the month
and year are easier to work with when evenly divisible. A quarter then has
90 days (30 * 3 or 360 / 4), and certain other equalities can be held true.
If you need exact date math, be prepared to spend a *lot* of time on the
problem. All exact date math operations must have a starting point, and
exact has different meanings depending on the application. Good luck.
On Aug 8, 2012 5:55 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
Dmitry Koterov wrote:
I've just discovered a very strange thing:
SELECT '1 mon'::interval = '30 days'::interval -- TRUE???
This returns TRUE (also affected when I create an unique index using
an
interval column). Why?
I know that Postgres stores monthes, days and seconds in interval
values
separately. So how to make = to compare intervals part-by-part
and not
treat 1 mon as 30 days?
P.S.
Reproduced at least in 8.4 and 9.1.
...and even worse:
SELECT ('1 year'::interval) = ('360 days'::interval); -- TRUE :-)
SELECT ('1 year'::interval) = ('365 days'::interval); -- FALSE :-)
Intervals are internally stored in three fields: months, days
and microseconds. A year has 12 months.
PostgreSQL converts intervals into microseconds before comparing them:
a month is converted to 30 days, and a day is converted to 24 hours.
Of course this is not always correct.
But what should the result of
INTERVAL '1 month' = INTERVAL '30 days'
be? FALSE would be just as wrong.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to create script of database in postgres..sql(winxp)
deepak pal wrote: hi i am trying to make script file for my database by using pg_dump in windows as u say i open psql to postgres then a prompt postgres# open then i write \i pg_dump it shows error.what should i do...plz hepl \i in psql is for including a file to be read and parsed by the database server. Surely, this is not what you are after. Since you have not specified the version of PostgreSQL that you're using, I'm going to take the liberty of assuming that you're using 8.1. http://www.postgresql.org/docs/8.1/interactive/backup.html That portion of the manual should address your needs with regards to using pg_dump and other utilities. It is the Backup/Restore chapter of the manual. - Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Database security granularity
Hello everyone, I'm working with an application, and I'm realizing that perhaps the model for security that I have used in the past won't work all that well with the application that I'm working on. I am certain that this particular model is how web applications traditionally work, but I am wondering if I can do something a little more then this in PostgreSQL. Traditionally, I have a table that contains the user names and hashed passwords for each of the users that are defined to the application. When they login, it checks against this table, and so forth. However, the application that I'm currently working on is something that I want to have a portable set of front-ends to work with. For example, I'd like a front-end in PHP for web-based access, but also I am working on creating a front-end that will be written in a more client/server fashion. I'd like to know if I can constrict database and data access on a row-level with PgSQL by using some sort of trickery in the database configuration itself. If not, that's okay, I suppose -- it'll just mean that I'll need to come up with my own client/server portions of the program, with the server side of it regulating the access. That will make it significantly more of a challenge, but it is the only other thing that I can come up with. Ideas? Thanks, Mike ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Syntax error, but where?
Hey guys, I'm having a slight problem with this database that I'm trying to setup on PostgreSQL 8.1.3... What I've got is a stored procedure that refuses to get itself into the system, and I'm not sure why. It is throwing a syntax error on DECLARE, but I don't see it. I looked at the documentation, and as far as I can tell, my CREATE FUNCTION line looks just as it should in structure, as does the CREATE TYPE line that is immediately before it. Any ideas? The code that is failing is: CREATE TYPE app_global.city_list AS (zip_code CHAR(5), city_name VARCHAR(40), state_abbr CHAR(2), distance_miles NUMERIC(6,3)); -- -- Stored Procedures in app_global -- CREATE FUNCTION app_global.get_zip_codes_range(zip_code CHAR(5), range_miles NUMERIC(4,1)) RETURNS SETOF city_list AS $$FUNC_BODY$$ DECLARE generic_cursor REFCURSOR; record_returned RECORD; record_to_return city_list%rowtype; Any help would be greatly appreciated! Thanks, Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Syntax error, but where?
Bricklen Anderson wrote: Is this actually part of the function: $$FUNC_BODY$$ ? If so, try it as $FUNC_BODY$ (single dollar signs around identifier). Oh, jeez. What an oversight. Thank you... I can't believe that I missed that. Sometimes, all that really is needed is a fresh pair of eyes. Thanks! - Mike ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] (Select *) vs. (Select id) from table.
Mike wrote: Hi, I am trying to make a website where scalability matters. In my quest to make my website more scalable I broke down the following SQL statement: select * from customers limit 100 to: select id, updated_date from customers limit 100 Then my application would check it's cache to see if it has those records available and will hit the database with consequent: select * from customers where id = 4 or id = 9 or id = 19 Am I really speeding things up by breaking down the SQL statements to what's necessary? or is it faster to get everything right at once! Well, first, it's never really a good idea to use SELECT * FROM in a production application, against a table. Tables can (and do) change from one release to another, and if the layout of the table changes, you could be looking at having to rewrite your code, especially if it relied on the order of the columns in the tables. It's always better to specify the columns that you're looking for, since existing columns should (at least in theory on a production DB) remain present, though their order can change sometimes, depending on what the DBA does. :) Secondly, as far as making your queries more efficient, the only way that you can really do that is to determine actually how long the queries are taking. This is relative to the size of the database in rows, and of course, the data that you're querying against, whether a table scan is necessary, and all of that. This is the process of optimizing queries. For a small table, it can be faster sometimes to just pull all of the records at once (for example, if they're all within a single page). However, if you're pulling from a large DB, it will be faster to use smaller queries against it, using well-placed indexes. Also, you may wish to consider using views if you really like using * with SELECT... Since this way, you can just depreciate a view and start using a new one if the underlying columns are changed. :) Also, if you use views, you can optimize the view's query when it comes time to change it, which mess less messing around in the application code, especially if it is a frequently used query. HTH, Mike ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
