Re: [SQL] definative way to place secs from epoc into timestamp
Just so I don't make a newb mistake I should use timestamptz not timestamp where the exact moment is important? My conversion which is not live yet is using timestamp as I did not clearly understand (but be very easy I hope to modify in my app that creates and moves the data just use timestamptz instead of timestamp). Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew - Supernews Sent: Friday, March 04, 2005 2:15 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] definative way to place secs from epoc into timestamp On 2005-03-04, Bret Hughes <[EMAIL PROTECTED]> wrote: >> Unix epoch times correspond to timestamp _with_ time zone. >> >> (Why are you using timestamp without time zone anyway? For recording the >> time at which an event occurred that usage is simply wrong - in fact I >> can't see any situation in which a Unix epoch time can correctly be >> converted to a timestamp without time zone.) > > Valid question. Because there is no reason to keep up with time zones It's a common mistake to think that just because you don't need to keep track of time zones that somehow using timestamp without time zone is correct. It is _not_. "timestamp with time zone" and "timestamp without time zone" have _very_ different semantics. One way to look at it is that "timestamp with time zone" designates a specific instant in absolute time (past or future). It is therefore the correct type to use for recording when something happened. In contrast, "timestamp without time zone" designates a point on the calendar, which has a different meaning according to where you are, and when. So the latter type crops up in some cases in calendar applications, and also in input/output conversions, but it's more often than not the _wrong_ type to use for storage, since the meaning changes with the timezone (and data _does_ get moved across timezones, whether due to physical relocation or other factors). Unix epoch times have the same semantics as "timestamp with time zone". > and the fact that I want the same value from the data base that I put > into it. "same" in which sense? The same absolute point in time? Or the same point on a calendar? Obviously if the timezone doesn't change, then the two are equivalent; but which one is your application actually looking for? (If your app is using Unix epoch times, then it's looking only at the absolute time and not the calendar time...) Here's an example of how it breaks (using your own conversion functions): test=> set timezone to 'UTC'; SET test=> insert into ttst values (int2ts(1109916954)); INSERT 887766166 1 test=> select ts,ts2int(ts) from ttst; ts | ts2int -+ 2005-03-04 06:15:54 | 1109916954 (1 row) (that is the correct UTC time corresponding to 1109916954) test=> set timezone to 'America/Denver'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int -+ 2005-03-04 06:15:54 | 1109942154 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int -+ 2005-03-04 06:15:54 | 1109934954 (1 row) Notice the value stored in the DB didn't change, but it suddenly means something different... In contrast, if you do the same thing with "timestamp with time zone", then the Unix time that you get back will _always_ be the same, as you would expect, regardless of the time zone. Using functions identical to yours except using "with time zone": test=> insert into tztst values (int2tsz(1109916954)); INSERT 889130554 1 test=> select ts,ts2int(ts) from tztst; ts | ts2int + 2005-03-04 06:15:54+00 | 1109916954 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int + 2005-03-04 01:15:54-05 | 1109916954 (1 row) test=> set timezone to 'America/Los_Angeles'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int + 2005-03-03 22:15:54-08 | 1109916954 (1 row) Notice that the stored timestamp doesn't actually change; it is displayed differently according to the timezone. The
Re: [SQL] definative way to place secs from epoc into timestamp
Andrew - Supernews <[EMAIL PROTECTED]> writes: > On 2005-03-04, Bret Hughes <[EMAIL PROTECTED]> wrote: >>> (Why are you using timestamp without time zone anyway? For recording the >> >> Valid question. Because there is no reason to keep up with time zones > It's a common mistake to think that just because you don't need to keep > track of time zones that somehow using timestamp without time zone is > correct. It is _not_. "timestamp with time zone" and "timestamp without > time zone" have _very_ different semantics. > [ excellent example snipped ] It's curious that people who say they want Unix timestamps find it so hard to wrap their heads around this, because in fact "timestamp with time zone" operates EXACTLY the way that Unix timekeeping is done. Consider this: $ export TZ=GMT $ date Fri Mar 4 15:11:31 GMT 2005 $ export TZ=EST5EDT $ date Fri Mar 4 10:11:35 EST 2005 The system's internal idea of the time didn't change (modulo the few seconds it took to type the commands), but the way it is displayed changed. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] definative way to place secs from epoc into timestamp
and the fact that I want the same value from the data base that I put into it. "same" in which sense? The same absolute point in time? Or the same point on a calendar? Obviously if the timezone doesn't change, then the two are equivalent; but which one is your application actually looking for? (If your app is using Unix epoch times, then it's looking only at the absolute time and not the calendar time...) Unix time stamps, short (int) or long res, are always supposed to GMT based, as far as I know - I never seen anything different, except maybe in homebrew software. So it should be both calendar and P.I.T. And you wouldn't need the TZ storage if the date-number and number-> translation itself takes the TZ arg so that it can localize the Human String for you. Ken ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] definative way to place secs from epoc into timestamp
Unix time stamps, short (int) or long res, are always supposed to GMT based, as far as I know - I never seen anything different, except maybe in homebrew software. So it should be both calendar and P.I.T. And you wouldn't need the TZ storage if the date-number and number-> translation itself takes the TZ arg so that it can localize the Human String for you. Ken In fact, I would suggest that if there is any function, or field, that takes a TZ-less argument (*especially* if it takes only the number), that its name should be made to contain 'UTC' so clearly disambiguate whats its intended use for (since zone-less values/fields SHOULD be regarded as UTC) - Otherwise, some users will place epoch numbers adjusted for the their timezone in the field (and even with daylight saving offsets applies, somewhat amusingly but wrong). So then two different users are using the exact same datatype for inconsistent types. (just a concern for interoperability, user awareness, and when an employee comes on-board and has to deal with bad legacy) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] date - date returns integer?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Should date - date return type integer, not interval?
/* [EMAIL PROTECTED]:5432/test =# */ SELECT ('2005-03-04'::timestamp -
'2005-01-01'::date)::interval;
~ interval
- --
~ 62 days
(1 row)
/* [EMAIL PROTECTED]:5432/test =# */ SELECT ('2005-03-04'::date -
'2005-01-01'::date)::interval;
ERROR: cannot cast type integer to interval
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFCKMwVgfzn5SevSpoRAlxAAJ9iPVf2yTNt11JBGc6Hun2s23+/MwCfYRwL
SzElfOrlIskOTAZucUdCeUE=
=jgOp
-END PGP SIGNATURE-
---(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] date - date returns integer?
On Fri, Mar 04, 2005 at 15:59:02 -0500, Andrew Hammond <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Should date - date return type integer, not interval? Yes. This is in the documentation. ---(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
[SQL] Simple delete takes hours
Hi there, I have a simple database: CREATE TABLE pwd_description ( id SERIALNOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL ); CREATE TABLE pwd_name ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), name varchar(50) NOT NULL, added timestamp DEFAULT now() ); CREATE TABLE pwd_name_rev ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE CASCADE, name varchar(50) NOT NULL ); The indexes shouldn't matter I think. pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) when something is inserted to pwd_name. Both tables contain about 4.500.000 emtries each. I stopped 'delete from pwd_name where description=1' after about 8 hours (!). The query should delete about 500.000 records. Then I tried 'delete from pwd_name_rev where description=1' - this took 23 seconds (!). Then I retried the delete on pwd_name but it's running for 6 hours now. I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz with 512 MB RAM. PostgreSQL should do a full table scan I think, get all records with description=1 and remove them - I don't understand what's happening for >8 hours. Any help is appreciated. Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres performance
Hi, thanks a lot! you are rigth, but I did read your message ;) Yes, 1- I misconfigured PostgreSQL (I thought that was already configured in base to the released version - Fedora Core 3 64bit). 2- The bench is, clearly after your precisations, an MySQL tuned application tests. 3- I think the bench test only one connection, I didn't see (in a fast reading) no threading request in the bench code to simulate users requests. 4- I didn't test transaction-safe (that isn't used explicitly in my application) I understand it isn't simple.. I use the dbms in data analysis environment and the more time is spent in query (php is 0.1%) with more sub-selects and maybe there's, in the same time, from 1 to 1000 users insert/update data. I tests the dbms with my data analysis framework simulating an super-extensive request. Do you know where I can find an tutorial to configure hardware dependent Postgres internal values? Thx, best regards,Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem with SQL_ASCII
I have a little problem in PostgreSQL 7.39 (and previous). Our database is in 'SQL_ASCII'-Format. When doing SQL-Selects all special Characters (e.g. äöüß, etc...) are ASCII encoded (sure they are). Is there any function to change the encoding to - let's say - LATIN1 (reverse function for to_ascii($text, 'LATIN1'))? Otherwise, is it possible to write a function which just uses a character replacement? I can image a select like: Name in Table contains 'Ernst & Young AG', select is: SELECT from_ascii(name, 'LATIN1') FROM table; And output should be 'Ernst & Young AG' We do this now in a second step with a find/replace tool... Anyone any idea? Thanks and *greets* Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] truncating table permissions
I was expecting "truncate table " to truncate a table if I had delete permissions. This does not appear to be the case. Would someone confirm this for me, or let me know what I am doing wrong. -- Lyn ---(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] Postgres performance
Hi Richard, thank you for your apreciated answers!!! - start quote - Well, do you care whether your data is consistent or not? If not, you don't need transactions. - end quote - I don't require transaction because the query aren't complex and update a single tuple (in SELECT transactions are useless) - start quote - You'll find inserts/updates with lots of users is where PostgreSQL works well compared to other systems. - end quote - Uhhmm.. this is interesting... - tutorial links - Thx, now I read it and test an hardware tuned configuration... I read that is not very simple... :O Another question: - why postgres release aren't already configured (hardware tuning)? isn't possible configure it during installation? - why postgres use a new process for every query ? (mySQL, if I'm not wrong, use threads... I think its faster) - why connection time is slower? (compared to mySQL)? - why postgres require analyze? (mySQL, if I'm not wrong, don't require it) Yours answers will be very apreciated! Thx ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Simple delete takes hours
On Thu, 3 Mar 2005, Thomas Mueller wrote: > Hi there, > > I have a simple database: > > CREATE TABLE pwd_description ( >id SERIALNOT NULL UNIQUE PRIMARY KEY, >name varchar(50) NOT NULL > ); > > CREATE TABLE pwd_name ( >id SERIALNOT NULL UNIQUE PRIMARY KEY, >description integer NOT NULL REFERENCES pwd_description(id), >name varchar(50) NOT NULL, >added timestamp DEFAULT now() > ); > > CREATE TABLE pwd_name_rev ( >id SERIALNOT NULL UNIQUE PRIMARY KEY, >description integer NOT NULL REFERENCES pwd_description(id), >rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE > CASCADE, >name varchar(50) NOT NULL > ); > > The indexes shouldn't matter I think. > > pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) > when something is inserted to pwd_name. Both tables contain about > 4.500.000 emtries each. > > I stopped 'delete from pwd_name where description=1' after about 8 hours > (!). The query should delete about 500.000 records. > Then I tried 'delete from pwd_name_rev where description=1' - this took > 23 seconds (!). > Then I retried the delete on pwd_name but it's running for 6 hours now. > > I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz > with 512 MB RAM. > > PostgreSQL should do a full table scan I think, get all records with > description=1 and remove them - I don't understand what's happening for > >8 hours. It's going to remove rows in pwd_name_rev based on the rev_of not description (and you really should make sure to have an index on rev_of). Without being able to see triggers and rules on the tables, I can't tell if it's even legal to remove the rows with description=1 from pwd_name_rev, but it isn't with just the constraints defined above. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres performance
I don't require transaction because the query aren't complex and update a single tuple (in SELECT transactions are useless) You mean, you have no foreign keys in your database ? In SELECT they are definitely useful (think select for update, isolation level serializable...) - start quote - You'll find inserts/updates with lots of users is where PostgreSQL works well compared to other systems. - end quote - Uhhmm.. this is interesting... pg does not lock the whole table everytime anyone wants to write in it. In MySQL when you run a big select, all write activity stops during that. If you run a big update, all activity other than this update has to wait. - why postgres use a new process for every query ? (mySQL, if I'm not wrong, use threads... I think its faster) Not for every query, for every CONNECTION. You are using persistant connections are you. Are you ? - why connection time is slower? (compared to mySQL)? This is of no importance as everyone uses persistent connections anyway. - why postgres require analyze? (mySQL, if I'm not wrong, don't require it) Yours answers will be very apreciated! Thx So it has a planner which knows what it's doing ;) instead of just guessing in the dark. And MySQL requires analyze too (read the docs), optimize table which looks like vacuum to me, and sometimes repair table... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Simple delete takes hours
Every time a row is removed from pwd_name, the ON DELETE CASCADE trigger will look in pwd_name_rev if there is a row to delete... Does it have an index on pwd_name_rev( rev_of ) ? If not you'll get a full table scan for every row deleted in pwd_name... On Thu, 03 Mar 2005 22:44:58 +0100, Thomas Mueller <[EMAIL PROTECTED]> wrote: Hi there, I have a simple database: CREATE TABLE pwd_description ( id SERIALNOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL ); CREATE TABLE pwd_name ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), name varchar(50) NOT NULL, added timestamp DEFAULT now() ); CREATE TABLE pwd_name_rev ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE CASCADE, name varchar(50) NOT NULL ); The indexes shouldn't matter I think. pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) when something is inserted to pwd_name. Both tables contain about 4.500.000 emtries each. I stopped 'delete from pwd_name where description=1' after about 8 hours (!). The query should delete about 500.000 records. Then I tried 'delete from pwd_name_rev where description=1' - this took 23 seconds (!). Then I retried the delete on pwd_name but it's running for 6 hours now. I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz with 512 MB RAM. PostgreSQL should do a full table scan I think, get all records with description=1 and remove them - I don't understand what's happening for >8 hours. Any help is appreciated. Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] definative way to place secs from epoc into timestamp
On 2005-03-04, "Joel Fradkin" <[EMAIL PROTECTED]> wrote: > Just so I don't make a newb mistake I should use timestamptz not timestamp > where the exact moment is important? Yes. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] truncating table permissions
Lynwood Stewart wrote: I was expecting "truncate table " to truncate a table if I had delete permissions. This does not appear to be the case. Would someone confirm this for me, or let me know what I am doing wrong. This is the case. You are not doing anything wrong. There was a discussion on this on the NOVICE list beginning on 2/22. The subject was "Question on TRUNCATE privileges" At the end of the day the answer is to have the table owner create a truncate function with SECURITY DEFINER privilege. The following is from Tom Lane. See CREATE FUNCTION. Something like (untested) create function truncate_my_table() returns void as $$ truncate my_table $$ language sql security definer; You'd probably then revoke the default public EXECUTE rights on this function, and grant EXECUTE only to selected users. -- Kind Regards, Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] definative way to place secs from epoc into timestamp
On Fri, 2005-03-04 at 01:35, Michael Glaesemann wrote: > > On Mar 4, 2005, at 14:47, Bret Hughes wrote: > > > On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote: > >> (Why are you using timestamp without time zone anyway? For recording > >> the > >> time at which an event occurred that usage is simply wrong - in fact I > >> can't see any situation in which a Unix epoch time can correctly be > >> converted to a timestamp without time zone.) > >> > > > > Valid question. Because there is no reason to keep up with time zones > > and the fact that I want the same value from the data base that I put > > into it. The app that this db supports is written in php and I kept > > getting something different out than what I put into it in the other > > passes I made while trying to get my head around this. the timestamps > > have historically been stored in flat files. > > > > > What goes in comes out. Gotta like it. > > I think the reason this works is because your webserver and your > postgresql server are in the same time zone, which is probably an > assumption made in a great-many cases. You may run into problems if at > some time the dbms and webserver are not in the same time zone and > you're relying on dbms-generated times (such as now() or > current_timestamp), or if the system > is relocated to another time zone. > > I think the following illustrates a problem that can occur if the > assumption that the time zone is not constant is no longer valid. > > Your system is working for you, so that's great. I just wanted to > explore this for myself a bit more -- I find the time zone related > material hard to get my head around myself :). Since I went through it, > I thought I'd share it with the list. > Thanks for the additional walk through. Thanks also to everyone else who has contributed to this thread and my education. I think I finally figured out what is what. Part of my issue has been that there are so many things that can affect the tz offset that is retrieved from the os via php or some other language I was looking to eliminate one of them. Examples that "stayed" in psql were not helping me on that point. Once I realized that the simple solution was indeed to stay in UTC (using gmmktime/gmstrftime rather than mktime/strftime in php for instance) and everyone's constructive criticism finally hammered the point. I have been humbled by this which my wife will tell you is not so bad a thing. I usually "get it" pretty quickly when tackling new concepts but this whole deal took me much longer than usual. I am working to alter the design now and since most of the db calls involving timestamps are contained in two php classes I should have it fixed this week end. Thanks again for everyone's patience and help. Bret ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] date - date returns integer?
Andrew Hammond <[EMAIL PROTECTED]> writes: > Should date - date return type integer, not interval? If we made it return interval then there would be all sorts of timezone dependencies introduced (think about DST crossings) ... which is generally something you don't want to think about when doing date arithmetic. I think the definitions of these operators are fine. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] truncating table permissions
"Lynwood Stewart" <[EMAIL PROTECTED]> writes: > I was expecting "truncate table " to truncate a table if I had > delete permissions. This does not appear to be the case. No, TRUNCATE is restricted to the table's owner, regardless of any grantable permissions. This was (ahem) inadequately documented until very recently. regards, tom lane ---(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
