Re: [SQL] How to show timestamp with milliseconds(3 digits) in Select
> > This is the query and result: > > select h323_setup_time from pv_legs_new where h323_setup_time = '2003-01-01 > 00:43:59.996-05'; > > h323_setup_time > --- > 2003-01-01 00:43:60.00-05 > > Actually, the real data of second and millisecond is 59.996-05, but it shows > 60.00. I failed to load it when I use this result in other query. > I'm using 7.3 and it works as expected: create table tstamptest ( tstampcol timestamp ); insert into tstamptest values ( '2003-01-01 00:43:59.996-05' ); select * from tstamptest ; tstampcol - 2003-01-01 00:43:59.996 The 7.1 doc does not say anything about microsecond representation nor storage, 7.3 has Note: When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6, since timestamp values are stored as seconds since 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When timestamps are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. So I can only offer the standard advice: upgrade to 7.3. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to show timestamp with milliseconds(3 digits) in Select clause in Ver7.1 ?
"Liang Luo" <[EMAIL PROTECTED]> writes: > 2003-01-01 00:43:60.00-05 > Actually, the real data of second and millisecond is 59.996-05, but it shows > 60.00. I failed to load it when I use this result in other query. This looks like an old bug. What Postgres version are you using, and on what platform? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Does anyone use TO_CHAR(INTERVAL)?
Tomasz, Peter, > I already know all of these things (look at thread "Formatting > intervals" dated on 2003-03-17). I was just thinking about intervals > idea. Keeping time inside interval is good. Keeping months inside > interval also looks good, but keeping both of them doesn't make sense. > It is impossible to use these fields together without referring them to > some real date. However illogical the current bahavior may seem, it *is* the SQL92/99 specification, so we're keeping it. I personally use INTERVAL heavily for calendaring applications. I got into a discussion with Thomas Lockhart last year about how INTERVAL treats daylight savings time -- which is seriously problematic from a calendar designer's perspective -- and was turned down by the core team wanting to change the behavior. Regrettably, even though the ANSI committee made a few mistakes with DATE/TIME, they *are* the ANSI committee and PostgreSQL as a project is very firmly committed to standards. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Complex outer joins?
Stephan Szabo <[EMAIL PROTECTED]> writes: > On 26 Mar 2003, Greg Stark wrote: >> Can you show an example where the join order would affect the result set? I >> can't think of any. > I can think of a few somewhat degenerate cases. I don't think you need a degenerate case. Consider from (G left join L on (G.SELID = L.SELID)) right join C on (L.SELID = C.SELID) versus from G left join (L right join C on (L.SELID = C.SELID)) on (G.SELID = L.SELID) In the former case you will see rows out for every SELID existing in C; in the latter case, rows out for every SELID existing in G, which may include rows having no match in C. The set of joined rows is the same in either case, but the set of rows added for unjoined keys differs. The difference between this and the query we were discussing is just use of "right" rather than "left" in the second JOIN. AIUI, in the Oracle syntax this difference would be expressed by moving the (*) from one side to the other of the L.SELID = C.SELID clause. Unless they have strange restrictions on the combinations of clauses you can mark with (*), I don't see how they can assume that join order is insignificant. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Off topic : world database
I would imagine that since neither the size of the earth, nor the laws of mathematics and geometry have any scheduled changes pending for their source code any time in the near future, it's reasonable to rely on the long/lat information and extrapolate the distances using basic planet earth dimensions. There are only so many degrees along the arc of any given sphere (360) and there are only so many minutes in a degree. The size of the earth represents only one number that needs to be looked up unless you care to include miles in addition to the international standard of km. If so, then you have an additional factor to consider in your calcualations. Am I making any sense? Tamir > -Original Message- > From: Michael A Nachbaur [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 26, 2003 11:55 AM > To: Ryan; [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Off topic : world database > > > On March 26, 2003 10:56 pm, Ryan wrote: > > Your best bet is buying a good zipcode database that has > lat/long, but > > would only really help you in the USA. This kind of data > tends not to be > > cheap... > > Either that, or web scrape mapquest.com or some other site > that provides > lat/long for returned results, but that probably violates the > acceptable use > policy of those sites. > > I for one would be very interested in such a web service (I'd > really like a > address / street lookup to find lat/longs for addresses, but > thats not likely > to happen). > > > ---(end of > broadcast)--- > TIP 3: 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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Complex outer joins?
On Wed, 26 Mar 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On 26 Mar 2003, Greg Stark wrote: > >> Can you show an example where the join order would affect the result set? I > >> can't think of any. > > > I can think of a few somewhat degenerate cases. > > I don't think you need a degenerate case. Consider > > from (G left join L on (G.SELID = L.SELID)) > right join C on (L.SELID = C.SELID) > > versus > > from G left join > (L right join C on (L.SELID = C.SELID)) > on (G.SELID = L.SELID) I'd noticed that too, but I was trying to do it without changing the type of join since I wasn't sure whether he'd meant one using only left joins or outer joins in general. > The difference between this and the query we were discussing is just use > of "right" rather than "left" in the second JOIN. AIUI, in the Oracle > syntax this difference would be expressed by moving the (*) from one > side to the other of the L.SELID = C.SELID clause. Unless they have > strange restrictions on the combinations of clauses you can mark with > (*), I don't see how they can assume that join order is insignificant. I'd guess that they might check that the conditions are in a specific set of limited conditions in order to allow the reordering and disallow the reordering otherwise (well, I'd hope that they do this if they ever allow reordering). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] EXECUTE problem on schema
Hi, all postgreSQL v7.3.3 I found EXECUTE on pl/pgsql doesn't support schema. When I specify a table such as "public.tablename", it reported "not such a relation object". The only SQL statement I used is "update". Don't know wether other statements have the same problem. Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Complex outer joins?
Tom Lane <[EMAIL PROTECTED]> writes: > The SQL-standard way of writing this would presumably be either > > from G left join L on (G.SELID = L.SELID) > left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL) I would think of it as this one. > from G left join > (L left join C on (L.SELID = C.SELID and L.SELLEVEL = C.SELLEVEL)) > on (G.SELID = L.SELID) I don't see how that would be at all different. > depending on which join you think ought to be done first. It might be > that the results are the same in this case, but I'm not convinced of > that. In general the results of outer joins definitely depend on join > order. I'm pretty sure Oracle actually builds an abstract join representation where the two queries above would actually be represented the same way. Then decides the order from amongst the equivalent choices based on performance decisions. Can you show an example where the join order would affect the result set? I can't think of any. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
