Обсуждение: 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!
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-081999-12-17 15:25:14-081999-12-17 15:25:29-081999-12-17 15:25:35-082000-01-1918:00:51-081999-12-17 15:27:02-081999-12-17 15:27:59-082000-01-19 18:00:54-081999-12-17 15:28:16-081999-12-1715: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-072000-06-02 06:16:08-072000-06-02 06:23:17-072000-06-02 06:23:17-072000-06-0206:23:17-072000-06-02 06:45:30-072000-06-02 06:49:55-072000-06-02 07:08:27-072000-06-02 07:13:21-072000-06-0207: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
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