[SQL] Problem in age on a dates interval
Hi all,
I'm using PostgreSQL 7.3.3 on i386-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.3 (Debian), and I don't understand the results of the following
queries:
SELECT age('2004-05-14 16:00'::timestamp,'2004-02-18 16:00'::timestamp);
age
2 mons 25 days
SELECT '2004-02-18 16:00'::timestamp+'2 mons 25 days'::interval;
?column?
-
2004-05-13 16:00:00
In this case, the age from 2004-05-14 16:00 to 2004-02-18 16:00 is 2
mons 25 days, but if I add the age to the initial date, it returns one
day less!?
SELECT age('2004-05-26 16:00'::timestamp,'2004-02-18 16:00'::timestamp);
age
---
3 mons 8 days
SELECT '2004-02-18 16:00'::timestamp+'3 mons 8 days'::interval;
?column?
-
2004-05-26 16:00:00
Here, the age between 2004-05-26 16:00 and 2004-02-18 16:00 is 3 mons 8
days, and this interval added to the initial date gives the correct result!!
Best regards,
Luis Sousa
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problem in age on a dates interval
Luis,
wow at first I thought I had my head around a leap
year problem so I advanced your query a year
testbed=# SELECT age('2005-05-14
16:00'::timestamp,'2005-02-18 16:00'::timestamp);
age
2 mons 24 days
(1 row)
testbed =# SELECT '2005-02-18 16:00'::timestamp +'2
mons 24 days'::interval;
?column?
-
2005-05-12 16:00:00
(1 row)
I just thought I would let you know it can get
worse. :) I don't know how daylight savings time
is playing this problem... but I didn't expect the
problem to grow like that.
Ted
--- Luis Sousa <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm using PostgreSQL 7.3.3 on i386-pc-linux-gnu,
> compiled by GCC gcc
> (GCC) 3.3 (Debian), and I don't understand the
> results of the following
> queries:
>
>
> SELECT age('2004-05-14 16:00'::timestamp,'2004-02-18
> 16:00'::timestamp);
> age
>
> 2 mons 25 days
>
> SELECT '2004-02-18 16:00'::timestamp+'2 mons 25
> days'::interval;
> ?column?
> -
> 2004-05-13 16:00:00
>
> In this case, the age from 2004-05-14 16:00 to
> 2004-02-18 16:00 is 2
> mons 25 days, but if I add the age to the initial
> date, it returns one
> day less!?
>
> SELECT age('2004-05-26 16:00'::timestamp,'2004-02-18
> 16:00'::timestamp);
> age
> ---
> 3 mons 8 days
>
> SELECT '2004-02-18 16:00'::timestamp+'3 mons 8
> days'::interval;
> ?column?
> -
> 2004-05-26 16:00:00
>
> Here, the age between 2004-05-26 16:00 and
> 2004-02-18 16:00 is 3 mons 8
> days, and this interval added to the initial date
> gives the correct result!!
>
__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---(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] Problem in age on a dates interval
Theodore Petrosky <[EMAIL PROTECTED]> writes: > wow at first I thought I had my head around a leap > year problem so I advanced your query a year I think what's going on here is a difference of interpretation about whether an "M months D days" interval means to add the months first or the days first. For instance 2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12 2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14 The timestamp-plus-interval operator is evidently doing addition the first way, but it looks like age() is calculating the difference in a way that implicitly corresponds to the second way. I have some vague recollection that this has come up before, but I don't recall whether we concluded that age() needs to be changed or not. In any case it's not risen to the top of anyone's to-do list, because I see that age() still acts this way in CVS tip. 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
[SQL] Sorting problem
Hi all, I have a problem sorting varchar fields. I will explain the problem with a simple example: this query select '##10' as sortfield union select '###1' as sortfield order by sortfield produces this correct output: '###1' '##10' but this one select '##107990' as sortfield union select '###17990' as sortfield order by sortfield produces this wrong output: '##107990' '###17990' I'm using postgresql 7.4.3 on Suse Linux 9.1. I created my db using: initdb --pgdata=../data/ --encoding=LATIN1 --lc-collate=it_IT --lc-ctype=it_IT Any suggestion? Tia Ruggero ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PHP] Secure DB Systems - How to
Thanks Bruno. I see the potential challenge. If the field value is encrypted, how can search be done for certain value? Do I have to seach for the encrypted value? If so, what password or key should I use? Thanks "Bruno Wolff III" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Thu, Jul 08, 2004 at 11:49:36 -0400, > Sarah Tanembaum <[EMAIL PROTECTED]> wrote: > > I was wondering if it is possible to create a secure database system > > usingPostgreSQL/PHP combination? > > > > I have the following in mind: > > > > I wanted to store all my( and my brothers and sisters) important document > > information such as birth certificate, SSN, passport number, travel > > documents, insurance(car, home, etc) document, and other important documents > > imagined in the database. > > > > The data will be entered either manually and/or scanned(with OCR). I need to > > be able to search on all the fields in the database. > > > > We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I > > maintained. The data should be synchronize/replicate between those > > computers. > > > > Well, so far it is easy, isn't it? > > > > Here's my question: > > > > a) How can I make sure that it secure so only authorized person can > > modify/add/delete the information? Beside transaction logs, are there any > > other method to trace any transaction(kind of paper trail)? > > Keeping the system administrator from seeing the data while making it > searchable is difficult. To do this you need to encrypt the data on > the client side using a key the client has (and this key has to be > protected from loss) and the only searches you can do are equality > searches using a hash or encrypted value. > > The system administrator can always delete the data. > > If you store which user has access to a row in the row, you can use views > to control access to the rows for people other than the system administrator. > > > b) How can I make sure that no one can tap the info while we are entering > > the data in the computer? (our family are scattered within US and Canada) > > Use SSL. > > > c) Is it possible to securely synchronize/replicate between our computers > > using VPN? Does PostgreSQL has this functionality by default? > > Probably the best thing here is to run one live server and make backups > of the system that you store at your relatives along with instructions > for recovering them if something happens to you. Probably the backups > should be encrypted with either the keys in your safe deposit box or > using a system where something like 3 out of 5 keys can be used to recover > the backup files. Be sure to test the backup recovery. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(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
Re: [SQL] Sorting problem
On Mon, 12 Jul 2004, Ruggero wrote: > Hi all, > I have a problem sorting varchar fields. > I will explain the problem with a simple example: > > this query >select '##10' as sortfield >union >select '###1' as sortfield >order by sortfield > produces this correct output: >'###1' >'##10' > > but this one > > select '##107990' as sortfield > union > select '###17990' as sortfield > order by sortfield > produces this wrong output: >'##107990' >'###17990' > > I'm using postgresql 7.4.3 on Suse Linux 9.1. > I created my db using: initdb --pgdata=../data/ --encoding=LATIN1 > --lc-collate=it_IT --lc-ctype=it_IT > > Any suggestion? it_IT looks like it's sorting based on the strings with the symbols removed. I get similar behavior on my system using the unix sort command with it_IT. This looks like it's a mismatch between what you want and the collation you've provided. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Query plan discrepancies
Hi, I have two databases that were created with identical schemas and
both filled in the exact same way, with the same indices etc., yet
they give different query paths for identical SELECTs. Normally this
wouldn't bug me, but one DB returns the select in relatively short
order, while the other one will hang for 15 minutes or so before I get
annoyed enough to kill it. Here's the kicker - yes, the table sizes in
the DBs is different, but the _larger_ database is the one that's
returning! This confuses me; thoughts?
- Bob
The statement:
select norm,count(norm) from medline_abstract_tokens where
pmid=7968456 and norm in (select norm_token from word_stats_base)
group by norm;
EXPLAIN from DB 1 (comes back):
QUERY PLAN
--
HashAggregate (cost=3282.48..3282.48 rows=1 width=8)
-> Nested Loop IN Join (cost=0.00..3282.35 rows=25 width=8)
-> Index Scan using medline_abstract_tokens_pmid on
medline_abstract_tokens (cost=0.00..6.67 rows=196 width=8)
Index Cond: (pmid = 7968456)
-> Index Scan using word_stats_base_norm on word_stats_base
(cost=0.00..3317.65 rows=1083 width=146)
Index Cond: (("outer".norm)::text =
(word_stats_base.norm_token)::text)
(6 rows)
EXPLAIN from DB 2 (doesn't come back):
QUERY PLAN
-
HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
-> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
-> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
-> Seq Scan on word_stats_base (cost=0.00..4126.09
rows=95109 width=146)
-> Index Scan using medline_abstract_tokens_norm on
medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
Index Cond: ((medline_abstract_tokens.norm)::text =
("outer".norm_token)::text)
Filter: (pmid = 7968456)
(7 rows)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] [ADMIN] [PHP] Secure DB Systems - How to
Daniel Struck wrote: If you decrypt the data on the database, the sysadmin can see it. Hm, you are right. If one does decrypt the data on the database you have to sent the password to postgresql and so a administrator of the database could easily grasb the password. So the only way to go, would be to perform en/decryption on the client side? Exactly. That is the only way to ensure that the data is never decrypted within the database or database server. Now, the 'client' IMHO is the PHP application. If the key for your encryption is stored in the user's session (on the webserver temporarily) then there is no log of that key or data (unless you store the session data in the database, then you got problems, see below). I'm starting an article on doing just this for International PHP Magazine, and of course will use PostgreSQL as the back-end ;-) I wonder now; if somebody could achieve to get a snapshot of the database, they could also be able to get the log-file of postgresql. So one would also have to make attention that the information like sql statements don't leak that way. Are there other places where this kind of information could leak? If you wanted the key based on each user, then you could use their password (which is typically stored in an MD5 hash) as the key for encryption/decryption, and put it in the session for use by the application while they are logged in. This is the easiest (and most effective, IMHO) way to keep the encryption at the user level, and keep the data in the database encrypted at all times. Basically every record would be encrypted with the key for the user associated with that record, and there's a lot of work for anyone with a snapshot who is working on brute forcing all that data row by row... :) The only time that data is not encrypted is on the webserver, and only during transmission of that data back to the client. SSL would be the most common approach to solving this problem. My absolute favourite DB layer for PHP is ADOdb, which also has a class that transparently stores your session data in the database (if desired). This is crucial for sites that have multiple webservers and load balancers, as your session data needs to be accessible from the webserver that you are currently at. The problem here is that the key for each user would also be stored in the database if this method were used, rendering your efforts pointless! OTOH, using the default storage of session data would put all the user keys in temp files on the hard drives of the webservers. Not only does this not scale well (as you have to tell pound or LocalDirector or whatever load balancer you use to stick each user to the primary server), but some would consider this absolutely not-acceptable, as any administrator on the servers could see that data. So I suppose you have to pick your poison on this one. I gotta figure this out so I can start writing ;^P -- Mitch ---(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] Sorting problem
Pop text in front of that first value and it works: template1=# select text '##107990' template1=# as "sortfield" template1-# union template1-# select '###17990' template1-# order by sortfield; sortfield -- ###17990 ##107990 (2 rows) Stu Ruggero wrote: > Hi all, > I have a problem sorting varchar fields. > I will explain the problem with a simple example: > > this query >select '##10' as sortfield >union >select '###1' as sortfield >order by sortfield > produces this correct output: >'###1' >'##10' > > but this one > > select '##107990' as sortfield > union > select '###17990' as sortfield > order by sortfield > produces this wrong output: >'##107990' >'###17990' > > I'm using postgresql 7.4.3 on Suse Linux 9.1. > I created my db using: initdb --pgdata=../data/ --encoding=LATIN1 > --lc-collate=it_IT --lc-ctype=it_IT > > Any suggestion? > Tia > Ruggero ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] BYTEA output presentation
I output the BYTEA datatype for a table in our database. The BYTEA data look like "/031/024/001/003?/022/". How can I use some PostgreSQL function to remove "/" when I use select statement ? What type of format is the BYTEA datatype? Can I output it to hexadecimal or octal format ? If you can, how ? Or do you know any third party tool or script which can output the hexadecimal or octal format for PostgreSQL's BYTEA datatype ? Your help is appreciated. Thank you. Peter Wang, ---(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
Re: [SQL] BYTEA output presentation
Peter Wang wrote: > The BYTEA data look like "/031/024/001/003?/022/". > How can I use some PostgreSQL function to remove "/" when I use > select statement ? What type of format is the BYTEA datatype? Can I > output it to hexadecimal or octal format ? If you can, how ? Or do > you know any third party tool or script which can output the > hexadecimal or octal format for PostgreSQL's BYTEA datatype ? Your > help is appreciated. Thank you. libpq has functions to deal with bytea data: http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query plan discrepancies
Have you run ANALYZE recently?
Please send back EXPLAIN ANALYZE for the below query.
> EXPLAIN from DB 2 (doesn't come back):
>QUERY PLAN
> -
> HashAggregate (cost=7763.55..7763.56 rows=1 width=8)
>-> Nested Loop (cost=4363.86..7763.55 rows=1 width=8)
> -> HashAggregate (cost=4363.86..4363.86 rows=200 width=146)
>-> Seq Scan on word_stats_base (cost=0.00..4126.09
> rows=95109 width=146)
> -> Index Scan using medline_abstract_tokens_norm on
> medline_abstract_tokens (cost=0.00..16.99 rows=1 width=8)
>Index Cond: ((medline_abstract_tokens.norm)::text =
> ("outer".norm_token)::text)
>Filter: (pmid = 7968456)
> (7 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
