Обсуждение: Weird datetime problem
I have 2 tables both of which have a datetime field and both have an identifier of date_submitted. I've been using(via PHP) a query of the following for a month or so with no problem: select count(*) from mytable1 where date_submitted > 'today'::datetime and select count(*) from mytable2 where date_submitted > 'today'::datetime Both queries were working fine until I dumped the data in mytable2 to file and reloaded it. Now I can enter a date and not get the rows that I put in today(or any other day). The date_submitted has a btree index on it. I've even dropped and created a new index and that still hasn't helped. I still come up with 0 rows found. All I'm trying to do is get the number of rows submitted since midnight. Oh, yeah, Postgres 6.4.2, PHP 3.0.7, Linux Slackware 2.0.35, Dual Pent 450's. Anyone have any ideas? Thanks in advance. Andy
It's not a timezone problem is it? Did the dates get re-loaded correctly? Andy Lewis wrote: > > I have 2 tables both of which have a datetime field and both have an > identifier of date_submitted. > > I've been using(via PHP) a query of the following for a month or so with > no problem: > > select count(*) from mytable1 where date_submitted > 'today'::datetime > > and > > select count(*) from mytable2 where date_submitted > 'today'::datetime > > Both queries were working fine until I dumped the data in mytable2 to > file and reloaded it. Now I can enter a date and not get the rows that I > put in today(or any other day). > > The date_submitted has a btree index on it. I've even dropped and created > a new index and that still hasn't helped. I still come up with 0 rows > found. > > All I'm trying to do is get the number of rows submitted since midnight. > > Oh, yeah, Postgres 6.4.2, PHP 3.0.7, Linux Slackware 2.0.35, Dual Pent > 450's. > > Anyone have any ideas? > > Thanks in advance. > > Andy
I was thinking that it may be a timezone thing. Some of the dates are of CDT and some are CST. I live in Dallas which is the Central Time Zone. I just don't understand. I seem to have alot of time/date problems with Postgres. Maybe its just me..... Actually, now I see what is going on. I defined this column as type datetime with a default to now(). At one time the default worked ok. Now it doesn't work at all. Perhaps this is a bug? If I insert a record and not state a date_submitted value the value that I get when I select that row is an eroneous date. However if I use now() even though it should default to now(), it works fine. Maybe this message would be better posted in the hackers list. Andy d, 12 May 1999, Chris Bitmead wrote: > It's not a timezone problem is it? Did the dates get re-loaded > correctly? > > Andy Lewis wrote: > > > > I have 2 tables both of which have a datetime field and both have an > > identifier of date_submitted. > > > > I've been using(via PHP) a query of the following for a month or so with > > no problem: > > > > select count(*) from mytable1 where date_submitted > 'today'::datetime > > > > and > > > > select count(*) from mytable2 where date_submitted > 'today'::datetime > > > > Both queries were working fine until I dumped the data in mytable2 to > > file and reloaded it. Now I can enter a date and not get the rows that I > > put in today(or any other day). > > > > The date_submitted has a btree index on it. I've even dropped and created > > a new index and that still hasn't helped. I still come up with 0 rows > > found. > > > > All I'm trying to do is get the number of rows submitted since midnight. > > > > Oh, yeah, Postgres 6.4.2, PHP 3.0.7, Linux Slackware 2.0.35, Dual Pent > > 450's. > > > > Anyone have any ideas? > > > > Thanks in advance. > > > > Andy >