pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate problem
10+ messages / 8 participants
[nested] [flat]
* date problem
@ 1998-09-19 21:53 Ian Johnston <ian@iprogc.demon.co.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Ian Johnston @ 1998-09-19 21:53 UTC (permalink / raw)
To: pgsql-sql
How do I select all fields where the 'date' entry is not set. Eg. items
who do not have a date stored in a date defined field ?
Ian Johnston
^ permalink raw reply [nested|flat] 10+ messages in thread
* Date problem
@ 2000-09-29 02:05 Edipo E. F. Melo <edipoelder@ig.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: Edipo E. F. Melo @ 2000-09-29 02:05 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Hi all,
Before all, a little (off-topic) comment:
>It's been my experience that Win32 ODBC does not drop
>connections until the database client is closed (e.g. MS
>Access is shut down).
"There is some things that only Micro$oft can do for you..."
Ok. Let's work. I posted a mail before explaining a strange
cituation if my Postgresql: when I use date_part() function to split
day, month and year of a date type column, it returns one day before.
In other words, '2000-01-01' returns day: 31, month:12, year: 1999.
I'm using OS/2 with Fix Pack 12, Postgresql 7.0.2.
Can any one explain this?
[]'s
Edipo Elder F. de Melo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date problem
@ 2000-09-29 05:41 Josh Berkus <josh@agliodbs.com>
parent: Edipo E. F. Melo <edipoelder@ig.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2000-09-29 05:41 UTC (permalink / raw)
To: pgsql-sql
Elipo,
> Ok. Let's work. I posted a mail before explaining a strange
> cituation if my Postgresql: when I use date_part() function to split
> day, month and year of a date type column, it returns one day before.
> In other words, '2000-01-01' returns day: 31, month:12, year: 1999.
No, I was hoping an expert would take this on. Lemme test it on Linux:
create table test_date AS (
haveadate DATE );
insert into test_date ( haveadate )
values ( '2000-04-30' );
select haveadate, date_part('month',haveadate),
date_part('day',haveadate),
date_part('year',haveadate) from test_date
haveadate
2000-04-30 4 30 2000
No problem here. Or on PG-ACCESS.
The problem must be in the OS/2 compile, probably some problem in
accessing the internal clock?
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date problem
@ 2000-09-29 06:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2000-09-29 06:24 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
>> Ok. Let's work. I posted a mail before explaining a strange
>> cituation if my Postgresql: when I use date_part() function to split
>> day, month and year of a date type column, it returns one day before.
>> In other words, '2000-01-01' returns day: 31, month:12, year: 1999.
> No problem here. Or on PG-ACCESS.
There's a known (and fixed for 7.1) bug of this sort that occurs on
spring daylight-savings-time transition days: the date-to-timestamp
conversion function picks the wrong timezone offset to interpret
"midnight" in, yielding a timestamp for 11PM of the prior date.
On the fall DST date, you get a timestamp for 1AM. For example,
in USA zones:
play=> select '2000-04-02'::date::timestamp;
?column?
------------------------
2000-04-01 23:00:00-05
(1 row)
play=> select '2000-10-29'::date::timestamp;
?column?
------------------------
2000-10-29 01:00:00-04
(1 row)
Not sure how this would apply to 2000-01-01, though. What timezone
are you in, anyway?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date problem
@ 2000-10-07 02:56 Edipo E. F. Melo <edipoelder@ig.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Edipo E. F. Melo @ 2000-10-07 02:56 UTC (permalink / raw)
To: pgsql-sql
On Fri, 29 Sep 2000 09:58:27 -0400, Tom Lane wrote:
Returning from previous thread, i found a solution for my
problem: "when everything fails, read the manual..."
Reading the OS/2 command reference, it says that TZ has three
parts: XXXCYYY. XXX - is the timezone name, C is the increment from UTC
time, and YYY is the name of summertime.
Ommiting the name of summertime (TZ=AST4), pgsql returned the
correct select.
>>> Not sure how this would apply to 2000-01-01, though. What timezone
>>> are you in, anyway?
>> OS/2 sets TZ=AST4ADT (I live in Brazil, Natal city).
>
>Hm. On a RedHat Linux box, with pgsql 7.0.2:
>
>regression=# show TimeZone ;
>NOTICE: Time zone is AST4ADT
>SHOW VARIABLE
>regression=# select '2000-01-01'::date::timestamp;
> ?column?
>------------------------
> 2000-01-01 00:00:00-04
[]'s
Edipo Elder F. de Melo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Date Problem
@ 2002-02-22 02:39 pauLSiew <paul@mqplayharder.com>
0 siblings, 1 reply; 10+ messages in thread
From: pauLSiew @ 2002-02-22 02:39 UTC (permalink / raw)
To: pgsql-sql
I'm using pgsql with php, and my query below will retrieve employee's
monthly sales total,
"select sum(salesTotal) from ocf where employeeNo='0123' AND orderDate like
'%$month $year %'" // i put % infront to ignore day, and % at the back to
ignore the time (because i'm using timestamp with timezone), then i shall
get particular month's records.
$month = Feb
$year = 2002
But i cant retrieve the data, if i take out the $year, it works...but i
need the year to identify the records.
it works when i do a direct sql query to the pgsql through pg-admin, the
query are like this,
select grand_total,ocf_no,date from ocf where date like '2002-01%' order by
date;
i really confused with the date format......
Anybody got any solution or better idea, pls reply! THANK YOU
Regards,
pauL
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date Problem
@ 2002-02-22 16:51 Josh Berkus <josh@agliodbs.com>
parent: pauLSiew <paul@mqplayharder.com>
0 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2002-02-22 16:51 UTC (permalink / raw)
To: pauLSiew <paul@mqplayharder.com>; pgsql-sql
Paul,
> I'm using pgsql with php, and my query below will retrieve employee's
> monthly sales total,
>
> "select sum(salesTotal) from ocf where employeeNo='0123' AND
> orderDate like
> '%$month $year %'" // i put % infront to ignore day, and % at the
> back to
> ignore the time (because i'm using timestamp with timezone), then i
> shall
> get particular month's records.
The main problem is that you are treating the TIMESTAMP field like it
is text. It is not. You cannot use comparitors designed for text
parsing (such as "LIKE '%xxxyyy%') on TIMESTAMPs and get reliable
results.
Instead, use functions designed for TIMESTAMPs:
http://www.postgresql.org/idocs/index.php?functions-datetime.html
For example, you could:
SELECT ... WHERE EXTRACT(month FROM orderdate) = $month AND
EXTRACT(year FROM orderdate) = $year;
... but there are a few ways you can answer this question. Read the
documentation page; better yet, buy and read a PostgreSQL book.
-Josh Berkus
P.S. In the future, questions of this type are more appropriate for the
NOVICE list. The SQL list is more for the
"how-do-I-get-this-insanely-complex-query-to-perform" type.
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Date Problem
@ 2003-05-01 06:36 Ramesh PAtel <ramesh@banas.guj.nic.in>
0 siblings, 2 replies; 10+ messages in thread
From: Ramesh PAtel @ 2003-05-01 06:36 UTC (permalink / raw)
To: pgsql-sql
Hi All
now Problem is
I have to date From = '2003-04-01' and To='2003-05-12'
now i want to use use date butween From and To.
and each date one bye one use in query. so how to
i increse date one bye one
Please Help me.
Ramesh Patel
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date Problem
@ 2003-05-01 08:51 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Ramesh PAtel <ramesh@banas.guj.nic.in>
1 sibling, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2003-05-01 08:51 UTC (permalink / raw)
To: Ramesh PAtel <ramesh@banas.guj.nic.in>; pgsql-sql
prepopulate a table with all possible dates,
say 10 years of data in a table of 3650 rows
regds
mallah.
On Thursday 01 May 2003 12:06 pm, Ramesh PAtel wrote:
> Hi All
>
> now Problem is
>
> I have to date From = '2003-04-01' and To='2003-05-12'
> now i want to use use date butween From and To.
> and each date one bye one use in query. so how to
>
> i increse date one bye one
>
> Please Help me.
>
> Ramesh Patel
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Date Problem
@ 2003-05-01 09:38 Stuart <sfbarbee@yahoo.com>
parent: Ramesh PAtel <ramesh@banas.guj.nic.in>
1 sibling, 0 replies; 10+ messages in thread
From: Stuart @ 2003-05-01 09:38 UTC (permalink / raw)
To: pgsql-sql
Ramesh,
If your field is a date field, you should be able to just do (From + 1),
(From +2)....
I know this works in 7.3.2 as in my example.. where published is a date field
select published,( published + 1), (published +2) from documents limit 10;
published | ?column? | ?column?
------------+------------+------------
2002-02-21 | 2002-02-22 | 2002-02-23
2002-02-26 | 2002-02-27 | 2002-02-28
2002-02-26 | 2002-02-27 | 2002-02-28
2002-02-25 | 2002-02-26 | 2002-02-27
Hope this helps.
Stuart
On Thursday 01 May 2003 02:36, Ramesh PAtel wrote:
> Hi All
>
> now Problem is
>
> I have to date From = '2003-04-01' and To='2003-05-12'
> now i want to use use date butween From and To.
> and each date one bye one use in query. so how to
>
> i increse date one bye one
>
> Please Help me.
>
> Ramesh Patel
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
"... the Zionist state of Israel has ignored many United Nations Security
Council Resolutions; possesses nuclear weapons and other weapons of mass
destruction; is currently engaged in a genocidal programme of mass killings
of Palestinians and that no attempt has been made by the United States or
Britain to disarm Israel or to stop its genocidal mass killings of
Palestinians" -- Result of Pan African Forum on Africans and the American
Imperialist War February 23, 2003
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-05-01 09:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-19 21:53 date problem Ian Johnston <ian@iprogc.demon.co.uk>
2000-09-29 02:05 Date problem Edipo E. F. Melo <edipoelder@ig.com.br>
2000-09-29 05:41 ` Re: Date problem Josh Berkus <josh@agliodbs.com>
2000-09-29 06:24 ` Re: Date problem Tom Lane <tgl@sss.pgh.pa.us>
2000-10-07 02:56 Re: Date problem Edipo E. F. Melo <edipoelder@ig.com.br>
2002-02-22 02:39 Date Problem pauLSiew <paul@mqplayharder.com>
2002-02-22 16:51 ` Re: Date Problem Josh Berkus <josh@agliodbs.com>
2003-05-01 06:36 Date Problem Ramesh PAtel <ramesh@banas.guj.nic.in>
2003-05-01 08:51 ` Re: Date Problem Rajesh Kumar Mallah <mallah@trade-india.com>
2003-05-01 09:38 ` Re: Date Problem Stuart <sfbarbee@yahoo.com>
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