pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
date comparision
3+ messages / 3 participants
[nested] [flat]

* date comparision
@ 2000-07-07 18:07 sandis <sandisj@parks.lv>
  2000-07-10 08:52 ` Re: date comparision ??? sandis <sandis@mediaparks.lv>
  0 siblings, 1 reply; 3+ messages in thread

From: sandis @ 2000-07-07 18:07 UTC (permalink / raw)
  To: pgsql-sql

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!





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: date comparision ???
  2000-07-07 18:07 date comparision sandis <sandisj@parks.lv>
@ 2000-07-10 08:52 ` sandis <sandis@mediaparks.lv>
  2000-07-12 16:55   ` Re: date comparision ??? Ross J. Reedstrom <reedstrm@rice.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: sandis @ 2000-07-10 08:52 UTC (permalink / raw)
  To: pgsql-sql

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 230000;
      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

jliang@ipinc.com
www.ipinc.com





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: date comparision ???
  2000-07-07 18:07 date comparision sandis <sandisj@parks.lv>
  2000-07-10 08:52 ` Re: date comparision ??? sandis <sandis@mediaparks.lv>
@ 2000-07-12 16:55   ` Ross J. Reedstrom <reedstrm@rice.edu>
  0 siblings, 0 replies; 3+ messages in thread

From: Ross J. Reedstrom @ 2000-07-12 16:55 UTC (permalink / raw)
  To: sandis <sandis@mediaparks.lv>; +Cc: pgsql-sql

On Mon, Jul 10, 2000 at 11:52:16AM +0300, sandis wrote:
> 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!

Right, that's the syntax for a datetime literal, not a cast. How about:


 SELECT datums_ FROM jaunumi WHERE flag = 'a' AND 
 date_part('year', datetime(datums_)) = 2000 LIMIT 1;

By the way, what version are you using? The functional cast is needed for 
6.5.X, but not for 7.X.

Ross

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-07-12 16:55 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-07 18:07 date comparision sandis <sandisj@parks.lv>
2000-07-10 08:52 ` Re: date comparision ??? sandis <sandis@mediaparks.lv>
2000-07-12 16:55   ` Re: date comparision ??? Ross J. Reedstrom <reedstrm@rice.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox