[SQL] date comparision
Is anyone out here can give some advice?
i have a table with a timestamp field.
i should know if there is records for the particular year and month
so i need a query like (in MySQL it was very simple, but i should move to
postgres!):
SELECT something FROM mytable WHERE MONTH('timestamp_field')=07
AND YEAR('timestamp_field')=2000 LIMIT 1;
So, i am looking for date/time functions:
SELECT * FROM table WHERE date_part('year',timestamp 'timestamp_field') =
2000;
but it doesn't work...(ERROR: Bad abstime external representation
'timestamp_field')
may be i need subselect or abstime(timestamp) function??
I suppose it's a lame q, but i cannot solve it today...
Thanks!
Re: [SQL] date comparision ???
Thanks for your input. Unfortunately, it doesn't helped..
Here is the samples.
This query works fine:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
'2000-06-02 06:11:01-07') = '2000' LIMIT 1;
datums_
--
2000-07-06 18:51:27+03
(1 row)
But this fails, obviously because the function doesnt know the value of
datums_:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
'datums_') = '2000' LIMIT 1;
ERROR: Bad datetime external representation 'datums_'
datums_ is a timestamp field. How do i get it's value to use in date_part()
function??
date_part('year',datums_) and date_part('year',datetime datums_) doesnt
work also!
- Original Message -
From: Jie Liang
To: sandis
Sent: Friday, July 07, 2000 11:19 PM
Subject: Re: [SQL] date comparision
Hi,
it works for me:
urldb=# \d deleted
Table "deleted"
Attribute |Type | Modifier
-+-+--
url | text|
allocatedto | varchar(30) |
deleteddate | timestamp |
id | integer | not null
Index: deleted_pkey
so , I 've a field deleteddate(type is timestamp) in table deleted.
urldb=# select deleteddate from deleted limit 10 offset 23;
deleteddate
1999-12-17 15:24:19-08
1999-12-17 15:25:14-08
1999-12-17 15:25:29-08
1999-12-17 15:25:35-08
2000-01-19 18:00:51-08
1999-12-17 15:27:02-08
1999-12-17 15:27:59-08
2000-01-19 18:00:54-08
1999-12-17 15:28:16-08
1999-12-17 15:28:20-08
urldb=# select deleteddate from deleted where year(deleteddate)=2000
and rtrim(monthname(deleteddate),' ')='June'
and rtrim(dayname(deleteddate),' ')='Friday' limit 10;
deleteddate
2000-06-02 06:11:01-07
2000-06-02 06:16:08-07
2000-06-02 06:23:17-07
2000-06-02 06:23:17-07
2000-06-02 06:23:17-07
2000-06-02 06:45:30-07
2000-06-02 06:49:55-07
2000-06-02 07:08:27-07
2000-06-02 07:13:21-07
2000-06-02 07:13:21-07
(10 rows)
Good luck!!!
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
[SQL] Simple concatenation in select query
Hello, Sorry for the stupid posting, but.. There was a string concatenation function in MySQL: SELECT CONCAT(first_name, " ", last_name) FROM table; Is there a similar function in Postgres? Certainly, it's possible to live without it, but i'd like to write as above, doing concatenation in place. Ok, it seems i found it now: SELECT TEXTCAT(first_name, last_name) FROM table; but it allows only 2 arguments, inserting " " or ' ' (space) causes an error: attribute ' ' not found! Why postgres doesnt see it as string? Ok, i got it, after all! It took > 30 min to write this query. :( SELECT textcat(textcat(first_name,text ' '),last_name) from table; It kind of strange, but i found this function not in "user manual", but doing "\df text".. There is some differences between function descriptions in manual and those that \df returns.. Virtually \df returns much more functions than in manual, and there is some differences in argument types. May be someone knows a better, complete manual with ALL function described and code samples? [EMAIL PROTECTED] www.mediaparks.lv
Re: [SQL] Extracting data by months
I do it like this:
SELECT datums FROM jaunumi
WHERE date_part('year',datetime(datums)) = '2000' AND
date_part('month',datetime(datums)) = '08';
Where datums is a timestamp field.
Regards,
Sandis Jerics
www.mediaparks.lv
> This might seem rather silly, but could you simply do something like:
>
> select * from database
>where date_field >= '01/01/2000'::date
>and date_field < '02/01/2000'::date;
>
> Of course, if date_field could contain many different years, then this
> would not get you the result you wanted.
>
> John
>
> On Thu, 3 Aug 2000, Antti Linno wrote:
>
> > Lo.
> >
> > I'm in dire need of knowledge, how to extract data by month. Monthday
> > and year arent' important, those I can't give from perl script, but what
> > I do give to postgres are the numbers of the months. Date field is in
> > timestamp. I thought about date_trunc, but I can't think of, how to get
> > data with it. In script will be 12 queries, for each month, to get the
> > news from that month.
> > Btw, is there somewhere a manual about date_trunc, the docs that come
with
> > RH6.1 distribution are somewhat short and lacking explanation of
> > date_trunc.
> > Antti
> >
> >
>
[SQL] data integrity
Hello pgsql-sql, sorry for my question that is obviously a kind of lame and not related directly to postges, but i never dealt with it before & need an idea what to do right now. there is a db with a few tables that have related (joined) fields. if record in a one table (auxiliary) gets deleted (along with it's primary key), reference to it in another table (main) points to nowhere. then doing a complex query with a join on that tables doesn't return such corrupted rows that contains references to deleted rows in other tables. what's the solution? should i check if this 'project', for example, have 'contacts' associated with it, and do not allow user to delete it, than show that 'contacts' list and ask if she wants delete all of them? or may be dont show a list, but just warn that there is $number 'contacts' related to this 'project', do you want to delete them all? how about joins in delete query? DELETE FROM projects, contacts WHERE projects.contact = contacts.id AND projects.id = '$id'; he, i wrote the answer myself. may be dont send it at all? can someone suggest some good online resource where such common sql topics discussed? thanks --:)-- Best regards, Sandis mailto:[EMAIL PROTECTED]
Re[2]: [SQL] data integrity
Hello Jesus, Tuesday, August 15, 2000, 4:04:40 PM, you wrote: JA> The solution is called referential integrity. you should use the foreign JA> key and references clause with cascade. >> can someone suggest some good online resource where such common sql topics >> discussed? JA> I would like to know such a place. JA> Jesus. Thank you. I found what i need about referential integrity here http://www.postgresql.org/mhonarc/pgsql-general/1999-08/msg00474.html ye,in Access it needed to check 1 checkbox, but here one should write some code.. --:)-- Best regards, Sandismailto:[EMAIL PROTECTED]
[SQL] Re: NULL
Hello, how must i write a NULL value in a text file for the \copy command to understand it? NULL, \0 doesnt work.. how must i write a boolean value in a text file for the \copy command to understand it? t doesnt work, however in a file written by /copy command it looks as t or f .. tried to search archives, but it seems deadly slow... Thanks in advance, sandis
[SQL] HELP! ... pg_locale ???
Hello folks,
I got a headache now, cause our admin played with postgres settings,
something about pg_locale, as he says. Perhaps the reason is
elsewhere, i dont know.
As result, now all queries, written inside the php code on multiply lines,
returns the following:
ERROR: parser: parse error at or near " "
The same query, pasted to psql, works well.
The same query written on single line in php code works well.
But i see that my older scripts with multiply line queries works well.
Whats up?! Why?
There is no errors in this code:
--
$result = @pg_exec($db,"
SET DATESTYLE = 'ISO';
SELECT DISTINCT
meznieciba,
date_part('day',date(datums)) AS dd,
date_part('month',date(datums)) AS mm,
date_part('year',date(datums)) AS ,
galvene,
kajene
FROM izsoles
WHERE datums = '$datums'") or die(pg_errormessage());
--
it works fine when written on the single line
but now i see the above stupid error message..
it so funny to have to rewrite queries to single line?..
--:)--
Best regards,
Sandis
[SQL] sorting the text values as integers
Hi,
i have a table with some text fields filled with a data like
100,23
235,12
500
200
the same fields somethimes contains the values like
100x100x25
125x125x50
200x80x90
and so on.
the client requires that rows are sorted in ascending order
for the case there are a float values, i do:
SELECT ... ORDER BY float4(field)
for the case there a text values, i do:
SELECT ... ORDER BY int2(substring(field from 1 for position('x' in field)-1));
so i can sort them ascendingly at least by the first integer (before
'x' char). otherwise (simply "ORDER BY field") they were sorted as text
values - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ...
now it sorted as i need - 10x10x10, 40x40x20, 100x100x30, 400x400x30 ...
it's almost fine, but...
now i need to combine that 2 cases, so i try (the field called m1):
SELECT ... ORDER BY (CASE WHEN position('x' in m1)>1 THEN int2(substring(m1 from 1
for position('x' in m1)-1)) ELSE float4(m1) END)
i never used CASE WHEN ... THEN ... ELSE ... END construct before,
& assume the above is errorneus by default.
--:)--
Best regards, Sandis
[SQL] i have a big trouble
Hello list, i expirienced that postgres seems doesn't want to eat it's own dump... why? even freshly created! whats wrong with me? www_mod_lv=> \copy news from '/home/sites/www.mod.lv/web/news.sql' \. ERROR: copy: line 2, pg_atoi: error in " At the international humanitarian mission of KFOR the Latvian unit participates with 10 soldiers from the National Armed Forces. The KFOR Latvian contingent - Military Police and medical units - will operate within the contingent of UK. " : can't parse " At the international humanitarian mission of KFOR the Latvian unit participates with 10 soldiers from the National Armed Forces. The KFOR Latvian contingent - Military Police and medical units - will operate within the contingent of UK. " PQendcopy: resetting connection i tried to do \copy from stdin, than throw in a one row of data, ended with \., it doesnt work also. This is my table: CREATE TABLE news( id SERIAL PRIMARY KEY, flag CHAR(1) NOT NULL, rubrika CHAR(1) NOT NULL, datums_ TIMESTAMP NOT NULL, datums VARCHAR(30) NOT NULL, virsraksts VARCHAR(200) NOT NULL, teksts TEXT, url VARCHAR(30), url_txt VARCHAR(50) ); an example row of data (tab separated) attached. anyone may have an idea? -- Sandis 609 9237502 45 a 2 2000-03-24 17:09:52+02 24.03.2000 ON THE RESULTS OF THE VISIT IN BRUSSELS Today on the 24th of March, the Minister of Defence, Mr. Ìirts Valdis Kristovskis, and Minister of Foreign Affairs, Mr. Indulis Bçrziòð, participated at the meeting of the North Atlantic Council and Latvia, led by the NATO Secretary General, Lord George Robertson. The Latvian representatives presented the Progress report on what has been accomplished and achieved in implementation of the Membership Action Plan (MAP) of Latvia for 2000. \ The Minister of Defence, Mr.Ìirts Valdis Kristovskis, in his speech informed ambassadors of the NATO member-countries, on what has been accomplished in the defence system of Latvia within MAP. Mr. Ì.V.Kristovskis noted, that Latvia's activity and full determination to participate in the process, is aimed at preparing Latvia to be ready to accept the invitation to join the North Atlantic Treaty Organisation at the next round of enlargement. At the same time Minister of Defence emphasised, that a lot still has to be done in improvement of the systems of procurement and military education. \ During the meeting it was noted that Latvia is one of the best NATO candidate - states as concerns the execution of the Membership Action Plan. It was also concluded at the session, that Membership Action Plan of Latvia for 2000 is well prepared, realistic, and financially well founded. The ambassadors of NATO member countries, acknowledged that Latvia has a systemic and purposeful approach in solving state defence related issues. \ Ambassadors of the NATO members states also positively evaluated Latvia's readiness to contribute to European security, more precisely - to participate by providing officers of the Latvian National Armed Forces for peace-keeping and peace - support missions in Bosnia - Hercegovina and Kosovo, as well as noting the close co-operation among the Baltic States themselves. \ Participants at the session also discussed the need for Latvia to enlarge her defence budget to 2% of the GDP, which is the average level of NATO countries. To this the Latvian representatives responded that the Government of Latvia has expressed the political will increasing state defence expenditures. Today at the session in Brussels, both the Minister of Defence, Ì.V.Kristovskis, and Minister of Foreign Affairs, once more stressed Latvia's strong determination to continue the initiated process of preparation for joining NATO.
