Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

7;Adrian Klaver *EXTERN*'; Rishi Gokhale; pgsql-general@postgresql.org Subject: RE: [GENERAL] date type changing to timestamp without time zone in postgres 9.4 Adrian Klaver wrote: On 05/30/2015 10:05 PM, Rishi Gokhale wrote: When I create a table with a column whose type is date the type gets

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

Rishi Gokhale wrote: > Thanks very much for your quick responses. I am indeed using EDB's postgres > plus. > > It looks like it has a function thats forcing the date type to change to a > timestamp. I actually > deleted that function, but it still didn't help. You shouldn't delete any functions

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

nks, Rishi From: Albe Laurenz Sent: Monday, June 1, 2015 3:32 AM To: 'Adrian Klaver *EXTERN*'; Rishi Gokhale; pgsql-general@postgresql.org Subject: RE: [GENERAL] date type changing to timestamp without time zone in postgres 9.4 Adrian Klaver wrote: > On 05/30/2015 10:05

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

Adrian Klaver wrote: > On 05/30/2015 10:05 PM, Rishi Gokhale wrote: >> When I create a table with a column whose type is date the type gets >> forced to timestamp without timezone after it gets created >> >> ops=# CREATE TABLE test ( >> ops(# namevarchar(40) NOT NULL, >> ops(# start dat

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

On 05/30/2015 10:05 PM, Rishi Gokhale wrote: When I create a table with a column whose type is date the type gets forced to timestamp without timezone after it gets created ops=# CREATE TABLE test ( ops(# namevarchar(40) NOT NULL, ops(# start date NOT NULL ops(# ); CREATE TABLE

[GENERAL] date type changing to timestamp without time zone in postgres 9.4

When I create a table with a column whose type is date the type gets forced to timestamp without timezone after it gets created ops=# CREATE TABLE test ( ops(# namevarchar(40) NOT NULL, ops(# start date NOT NULL ops(# ); CREATE TABLE ops=# \d test; Table "public.

Re: [GENERAL] date with month and year

On 22/05/2015 06:09, Adrian Klaver wrote: > On 05/21/2015 09:04 PM, Alvaro Herrera wrote: >> Adrian Klaver wrote: >> >>> SELECT >>> extract ( >>> YEAR >>> FROM >>> school_day ) AS YEAR, >> >>> Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). >> >> FWIW

Re: [GENERAL] date with month and year

On Fri, May 22, 2015 at 09:46:10AM +0100, Tim Clarke wrote: > On 22/05/15 09:40, Alban Hertroys wrote: > > On 21 May 2015 at 23:42, Karsten Hilbert wrote: > > > >> You are right in the following aspect: > >> > >> - client sends in "NOW at HERE" > >> - server knows HERE = UTC+2 > > And then the te

Re: [GENERAL] date with month and year

On 22/05/15 09:40, Alban Hertroys wrote: > On 21 May 2015 at 23:42, Karsten Hilbert wrote: > >> You are right in the following aspect: >> >> - client sends in "NOW at HERE" >> - server knows HERE = UTC+2 > And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or > +3 > > Thank

Re: [GENERAL] date with month and year

On 21 May 2015 at 23:42, Karsten Hilbert wrote: > You are right in the following aspect: > > - client sends in "NOW at HERE" > - server knows HERE = UTC+2 And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or +3 Thankfully, those things don't shift as fast as they sometim

Re: [GENERAL] date with month and year

On 05/21/2015 09:04 PM, Alvaro Herrera wrote: Adrian Klaver wrote: SELECT extract ( YEAR FROM school_day ) AS YEAR, Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). FWIW I think this indenting of FROM inside an extract() call is odd and ugly -

Re: [GENERAL] date with month and year

Adrian Klaver wrote: > SELECT > extract ( > YEAR > FROM > school_day ) AS YEAR, > Reformatting courtesy of pgFormatter(http://sqlformat.darold.net/). FWIW I think this indenting of FROM inside an extract() call is odd and ugly --- probably just an accident resulting from

Re: [GENERAL] date with month and year

On 05/21/2015 11:56 AM, Steve Crawford wrote: On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgres

Re: [GENERAL] date with month and year

On 05/21/2015 11:02 AM, Daniel Torres wrote: Sorry, forgot to told you what I'm trying, I have climate data and want to obtain mean temperature and total precipitation and that sort of things per month and year. Think date_trunc is a good solution, but any other advice would be very welcome. As

Re: [GENERAL] date with month and year

> It's probably worth noting that both the Ruby 'best practice' AND > Postgres have a failure case when dealing with future dates precisely > because they are storing the data as UTC with a time zone. This is > one case where storing the data WITHOUT TIME ZONE would actually save > your bacon. >

Re: [GENERAL] date with month and year

On Thu, May 21, 2015 at 5:27 PM, Thomas Kellerer wrote: > Postgres does not store the time zone. When storing a timestamp with time > zone, it > is normalized to UTC based on the timezone of the client. When you retrieve > it, > it is adjusted to the time zone of the client. > Sorry, I misspoke.

Re: [GENERAL] date with month and year

what you've said above is incorrect. All "WITH TIME ZONE" does is tell PostgreSQL to apply timezone conversions during various operations. The stored data is represented as an epoch without any concept of the source data's timezone representation. Oh, very interesting! Thank you for pointing th

Re: [GENERAL] date with month and year

> Anyway, I agree that you have to store the time zone *somewhere*, and I > suppose that's the reason Joshua remarked that you really shouldn't use > WITHOUT TIME ZONE. And often a time has one perspective that is > "canonical" or "preferred", e.g. the time zone of the user who created > the ob

Re: [GENERAL] date with month and year

Brian Dunavant wrote on 21.05.2015 21:51: It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE w

Re: [GENERAL] date with month and year

On Thu, May 21, 2015 at 2:10 PM, Paul Jungwirth wrote: > Anyway, I agree that you have to store the time zone *somewhere*, and I > suppose that's the reason Joshua remarked that you really shouldn't use > WITHOUT TIME ZONE. And often a time has one perspective that is "canonical" > or "preferred"

Re: [GENERAL] date with month and year

Hi Steve, Thanks for such a thorough response! I agree that time is a lot trickier and trappier than one might expect, so it's good to learn how others grapple with it. > Your original question had to do with month/year. Just to clarify, that was Daniel's original question, but you're reply

Re: [GENERAL] date with month and year

On May 21, 2015 11:56:52 AM Steve Crawford wrote: > The article does also display a couple attitudes that I feel are especially > rampant in the web-development community. The first is that web developers > shouldn't become educated about the capabilities of a database but rather > use the database

Re: [GENERAL] date with month and year

It's probably worth noting that both the Ruby 'best practice' AND Postgres have a failure case when dealing with future dates precisely because they are storing the data as UTC with a time zone. This is one case where storing the data WITHOUT TIME ZONE would actually save your bacon. >From the po

Re: [GENERAL] date with month and year

On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgresql.org/docs/current/static/datatype-datetime.

Re: [GENERAL] date with month and year

Sorry, forgot to told you what I'm trying, I have climate data and want to obtain mean temperature and total precipitation and that sort of things per month and year. Think date_trunc is a good solution, but any other advice would be very welcome. (I need to read more about time zones, I'm new at

Re: [GENERAL] date with month and year

You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Are there any articles you'd recommend? I'm fond of normalizing all times to UTC and only presenting them in a time zone when I know the current "perspective". I've written about that approach in a

Re: [GENERAL] date with month and year

On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column?... Others have offered good tips bu

Re: [GENERAL] date with month and year

On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result

Re: [GENERAL] date with month and year

On Thursday, May 21, 2015, Daniel Torres wrote: > I everybody, I'm new in the Postgresql world, and have an easy question: > Is it possible to have date type data that only contain month and year?, > how can I obtain that from a timestamp (without time zone) column? > > I've made this, but I thin

Re: [GENERAL] date with month and year

Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I think you want date_trunc, which will cut everything down to the first of the month, e.g 2015-01-01, 2015-02-01, etc. The results will still be dates, s

Re: [GENERAL] date with month and year

On Thu, May 21, 2015 at 12:01 PM, Daniel Torres wrote: > I everybody, I'm new in the Postgresql world, and have an easy question: > Is it possible to have date type data that only contain month and year?, > how can I obtain that from a timestamp (without time zone) column? > > I've made this, but

[GENERAL] date with month and year

I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column? I've made this, but I think the result is a text, not a date select extract (Year fr

Re: [GENERAL] DATE type output does not follow datestyle parameter

On 07/26/2013 01:24 PM, MauMau wrote: > From: "Adrian Klaver" >> Actually the relevant code seems to be here: >> >> /src/ backend/parser/gram.y >> >> >> CURRENT_DATE >>{ >>/* >> * Transl

Re: [GENERAL] DATE type output does not follow datestyle parameter

From: "Adrian Klaver" Actually the relevant code seems to be here: /src/ backend/parser/gram.y CURRENT_DATE { /* * Translate as "'now'::text::date".

Re: [GENERAL] DATE type output does not follow datestyle parameter

On 07/26/2013 05:31 AM, MauMau wrote: > Hello, > > The description of datestyle parameter does not seem to match the actual > behavior. Is this a bug to be fixed? Which do you think should be > corrected, the program or the manual? > > > The manual says: > > DateStyle (string) > Sets the disp

[GENERAL] DATE type output does not follow datestyle parameter

Hello, The description of datestyle parameter does not seem to match the actual behavior. Is this a bug to be fixed? Which do you think should be corrected, the program or the manual? The manual says: DateStyle (string) Sets the display format for date and time values, as well as the rules f

Re: [HACKERS] [GENERAL] Date conversion using day of week

Patch applied. --- On Sat, Sep 1, 2012 at 05:14:39PM -0400, Bruce Momjian wrote: > [Properly posted to hackers list] > > On Fri, Apr 1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote: > > On 1 April 2011 02:00, Adrian Klave

Re: [HACKERS] [GENERAL] Date conversion using day of week

[Properly posted to hackers list] On Fri, Apr 1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote: > On 1 April 2011 02:00, Adrian Klaver wrote: > > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: > >> If we wanted to make it "work", then I think the thing to do would be > >> to add a ne

Re: [HACKERS] [GENERAL] Date conversion using day of week

On Fri, Apr 1, 2011 at 03:58:48AM +1100, Brendan Jurd wrote: > On 1 April 2011 03:32, Adrian Klaver wrote: > > Now I am confused the docs say: > > > > D       day of the week, Sunday(1) to Saturday(7) > > ID      ISO day of the week, Monday(1) to Sunday(7) > > > > This would seem to say they both

Re: [HACKERS] [GENERAL] Date conversion using day of week

On Fri, Apr 1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote: > On 1 April 2011 02:00, Adrian Klaver wrote: > > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: > >> If we wanted to make it "work", then I think the thing to do would be > >> to add a new set of formatting tokens IDY, IDA

Re: [GENERAL] Date Range Using Months and Days Only

:37 AM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Thu, Oct 20, 2011 at 8:42 AM, Jeff Adams wrote: Thanks for the reply and assistance. I share your concern that the approach may be slow. I am not adverse to creating some

Re: [GENERAL] Date Range Using Months and Days Only

[mailto:adam.corn...@gmail.com] Sent: Wednesday, October 19, 2011 6:51 PM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Range Using Months and Days Only On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams wrote: Greetings, I have to write a query on a fairly large table of data (>

Re: [GENERAL] Date Range Using Months and Days Only

over 0 would indicate that > the date does fall within the range? > > From: Adam Cornett [mailto:adam.corn...@gmail.com] > Sent: Wednesday, October 19, 2011 6:51 PM > To: Jeff Adams > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Date Range Using Months and Days O

Re: [GENERAL] Date Range Using Months and Days Only

On Wed, Oct 19, 2011 at 12:40 PM, Jeff Adams wrote: > Greetings, > > I have to write a query on a fairly large table of data (>100 million rows) > where I need to check to see if a date (epoch) falls between a range of > values. The catch is that the range is defined only by month and day > value

[GENERAL] Date Range Using Months and Days Only

Greetings, I have to write a query on a fairly large table of data (>100 million rows) where I need to check to see if a date (epoch) falls between a range of values. The catch is that the range is defined only by month and day values. For example the record containing the epoch value will be link

Re: [GENERAL] Date time value error in Ms Access using pass through queries

I found the solution. Earlier for 9.0 database I have changed a line as Datestyle= ' ISO, mdy' to DateStyle= "ISO, dmy'. So it was working fine. For 9.1 I forgot to make this change in postgresql.conf. After making the change everything is working fine. Regards, C P Kulkarni On Fri, Sep 23, 2011

Re: [GENERAL] Date time value error in Ms Access using pass through queries

As I have connected to postgres from MS Access, it thrown the error. Now I have updated ODBC driver, still same problems comes for 9.1 and not for 9.0. There should be some thing that has changed in 9.1 release which prevents from auto-conversion of format of date from client to server. >From packa

Re: [GENERAL] Date time value error in Ms Access using pass through queries

On Thursday, September 22, 2011 2:25:40 am c k wrote: > It shows error as 'Date/time value out of range' and gives the actual part > of the sql statement which contains date as the details. What program threw the error, Access,ODBC or Postgres? > > I have installed both databases at different lo

Re: [GENERAL] Date time value error in Ms Access using pass through queries

It shows error as 'Date/time value out of range' and gives the actual part of the sql statement which contains date as the details. I have installed both databases at different locations on same drive on Fedora 15 and accessing it from Windows Xp virtual machine. And both databases from 9.0. and 9

Re: [GENERAL] Date time value error in Ms Access using pass through queries

On Wednesday, September 21, 2011 3:08:11 am c k wrote: > Hello, > > I got a error yesterday while I have updated my development database to 9.1 > from 9.0.4. I backed up my database from 9.0 and restored in 9.1. It well > successfully. When running a query which calls a function, having date > val

[GENERAL] Date time value error in Ms Access using pass through queries

Hello, I got a error yesterday while I have updated my development database to 9.1 from 9.0.4. I backed up my database from 9.0 and restored in 9.1. It well successfully. When running a query which calls a function, having date value as IN parameter, it gives me the error as date/time value out of

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 1 April 2011 05:16, Steve Crawford wrote: > Well, to return to the original issue, should we allow the day to be spelled > out and fix it (as noted in this thread it is non-standard but also > unambiguous and we already allow plenty of non-standard formats) or throw an > error? For me personall

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 03/31/2011 10:51 AM, Brendan Jurd wrote: I agree with your summary of the ISO standards. Unfortunately, to_date and its cohorts are not targeting ISO. They are targeting quasi-compatibility with some Oracle functions of the same name, I suppose to make life easier for folks who are migratin

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 1 April 2011 04:16, Steve Crawford wrote: > This whole discussion opens a #10 sized can o' worms. Admittedly, I don't > have good knowledge of any SQL-mandated interpretations of an ISO date - but > based on my reading of ISO formatting I see the following issues: > > 1. What we describe in the

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 03/31/2011 08:00 AM, Adrian Klaver wrote: On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: On 31 March 2011 03:15, Steve Crawford wrote: On 03/29/2011 04:24 PM, Adrian Klaver wrote: ... Well the strange part is only fails for SUN:... test(5432)aklaver=>select to_date('2011-13-SU

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 1 April 2011 03:32, Adrian Klaver wrote: > Now I am confused the docs say: > > D       day of the week, Sunday(1) to Saturday(7) > ID      ISO day of the week, Monday(1) to Sunday(7) > > This would seem to say they both are one-based but differ on the day that is > 1. That's correct for the us

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 03/31/2011 08:27 AM, Brendan Jurd wrote: On 1 April 2011 02:00, Adrian Klaver wrote: On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: If we wanted to make it "work", then I think the thing to do would be to add a new set of formatting tokens IDY, IDAY etc. I don't like the idea

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 1 April 2011 02:35, Marc Munro wrote: > Just to be clear, the reason I was mixing things in this way was that I > wanted to validate that the dayname being passed was valid for the > current locale, and I could find no easier way of doing it. Ah, I see. In that case I think to_date would have

Re: [HACKERS] [GENERAL] Date conversion using day of week

On Thu, 2011-03-31 at 08:00 -0700, Adrian Klaver wrote: > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: > > On 31 March 2011 03:15, Steve Crawford > > wrote: > > > On 03/29/2011 04:24 PM, Adrian Klaver wrote: > > >> ... > > >> Well the strange part is only fails for SUN:... [. . .]

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 1 April 2011 02:00, Adrian Klaver wrote: > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: >> If we wanted to make it "work", then I think the thing to do would be >> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the >> idea of interpreting DY and co. differentl

Re: [HACKERS] [GENERAL] Date conversion using day of week

On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: > On 31 March 2011 03:15, Steve Crawford wrote: > > On 03/29/2011 04:24 PM, Adrian Klaver wrote: > >> ... > >> Well the strange part is only fails for SUN:... > >> test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY'); > >> to_

Re: [HACKERS] [GENERAL] Date conversion using day of week

On 31 March 2011 03:15, Steve Crawford wrote: > On 03/29/2011 04:24 PM, Adrian Klaver wrote: >> ... >> Well the strange part is only fails for SUN:... >> test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY'); >>   to_date >> >>  2011-03-28 >> ... > > You specified Sunday as t

Re: [GENERAL] Date conversion using day of week

On 03/30/2011 09:15 AM, Steve Crawford wrote: On 03/29/2011 04:24 PM, Adrian Klaver wrote: ... Well the strange part is only fails for SUN:... test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY'); to_date 2011-03-28 ... You specified Sunday as the day but the date return

Re: [GENERAL] Date conversion using day of week

On 03/29/2011 04:24 PM, Adrian Klaver wrote: ... Well the strange part is only fails for SUN:... test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY'); to_date 2011-03-28 ... You specified Sunday as the day but the date returned is a Monday. I would categorize that as

Re: [GENERAL] Date conversion using day of week

On Tuesday, March 29, 2011 9:02:52 am Steve Crawford wrote: > > But you changed it to specify an ISO year avoiding the mixed > conventions. According to the 9.0 docs > (http://www.postgresql.org/docs/9.0/static/functions-formatting.html): > > "An ISO week date (as distinct from a Gregorian dat

Re: [GENERAL] Date conversion using day of week

On 03/29/2011 08:50 AM, Adrian Klaver wrote: On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote: On 03/29/2011 08:07 AM, Marc Munro wrote: I'm trying to validate a day of the week, and thought that to_date would do the job for me. But I found a case where it cannot tell the difference

Re: [GENERAL] Date conversion using day of week

On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote: > On 03/29/2011 08:07 AM, Marc Munro wrote: > > I'm trying to validate a day of the week, and thought that to_date would > > do the job for me. But I found a case where it cannot tell the > > difference between sunday and monday. Is this

Re: [GENERAL] Date conversion using day of week

On 03/29/2011 08:07 AM, Marc Munro wrote: I'm trying to validate a day of the week, and thought that to_date would do the job for me. But I found a case where it cannot tell the difference between sunday and monday. Is this a bug or intended behaviour? dev=# select to_date('2011-13-Mon', '

Re: [GENERAL] Date conversion using day of week

On Tuesday, March 29, 2011 8:07:48 am Marc Munro wrote: > I'm trying to validate a day of the week, and thought that to_date would > do the job for me. But I found a case where it cannot tell the > difference between sunday and monday. Is this a bug or intended > behaviour? > > dev=# select to_d

[GENERAL] Date conversion using day of week

I'm trying to validate a day of the week, and thought that to_date would do the job for me. But I found a case where it cannot tell the difference between sunday and monday. Is this a bug or intended behaviour? dev=# select to_date('2011-13-Mon', '-IW-DY'); to_date 2011-03

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

In response to "Kurt Westerfeld" : > >>> Radosław Smogura 1/4/2011 9:48 AM >>> > Can You try "...BETWEEN ?::date and ?::date ..." syntax or send > statement causing problems? > > As for the BETWEEN clause, I'm using hibernate and don't want to put a > database-specific SQL query in place. You

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

First, sorry I didn't mention that I am using PostgreSQL 9, but the problem existed also on 8.4. As for the BETWEEN clause, I'm using hibernate and don't want to put a database-specific SQL query in place. I also decided to drop a few indexes, which were probably causing the optimizer to c

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

Can You try "...BETWEEN ?::date and ?::date ..." syntax or send statement causing problems? Kind regards, Radosław Smogura On Tue, 04 Jan 2011 07:27:42 -0700, "Kurt Westerfeld" wrote: "By the very definition of a prepared statement the query plan gets stored before the parameter values are k

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

"By the very definition of a prepared statement the query plan gets stored before the parameter values are known" Is this true for all databases? It would seem to me that this approach would always lead to the wrong query plan, especially in the case I am testing where the selectivity is very

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote: > I have a JDBC-based application which passes date/time parameters using JDBC > query parameters, which is performing very badly (ie. doing full table > scans). In an effort to try to narrow down the problem, I am taking the > query and running

Re: [GENERAL] Date Parameter To Query Confusing Optimizer

On Mon, Jan 3, 2011 at 2:48 PM, Kurt Westerfeld wrote: > I have a JDBC-based application which passes date/time parameters using JDBC > query parameters, which is performing very badly (ie. doing full table > scans).  In an effort to try to narrow down the problem, I am taking the > query and runn

[GENERAL] Date Parameter To Query Confusing Optimizer

I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing very badly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and running it in interactive SQL mode, but changing the date paramet

[GENERAL] Date comparison without a year

Hello There is a table with user reviews about hotels, every row has columns: time_of_travel_begin and time_of_travel_end. User can select date range (interval), for example 15 June - 10 July and DB request should return all reviews for any years (for 15 June - 10 July 2010, 15 June - 10 July 2009

Re: [GENERAL] Date with time zone

On Mon, Nov 30, 2009 at 7:22 AM, Martijn van Oosterhout wrote: > On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote: > > Analysis of the extra complications added by DST's does not add anything, > > yet, to the point I'm trying to make, regardless the lack of such cases > in > > prac

Re: [GENERAL] Date with time zone

On Sunday 29 November 2009 8:51:33 pm Eduardo Piombino wrote: > > Just sharing some thoughts. > 1. That current "date" datatype is actually an abstract definition of a > time range. Since it is not localized (put in any time zone), it defines a > time range going from 00:00:00 hs to 23:59:59.

Re: [GENERAL] Date with time zone

On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote: > Analysis of the extra complications added by DST's does not add anything, > yet, to the point I'm trying to make, regardless the lack of such cases in > practice. The major problem with timezone support in SQL is that they basical

Re: [GENERAL] Date with time zone

On Sun, Nov 29, 2009 at 8:23 PM, Adrian Klaver wrote: > On Sunday 29 November 2009 2:38:43 pm Eduardo Piombino wrote: > > On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver > wrote: > > > On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: > > > > Hi Adrian, thanks for your answer. > > >

Re: [GENERAL] Date with time zone

On Sunday 29 November 2009 2:38:43 pm Eduardo Piombino wrote: > On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver wrote: > > On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: > > > Hi Adrian, thanks for your answer. > > > > > > I see current criteria and all the SQL-standard compliance

Re: [GENERAL] Date with time zone

On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver wrote: > On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: > > Hi Adrian, thanks for your answer. > > > > I see current criteria and all the SQL-standard compliance policy, but > > wouldn't it still make sense to be able to store a date

Re: [GENERAL] Date with time zone

On Sat, Nov 28, 2009 at 4:57 PM, Tom Lane wrote: > Eduardo Piombino writes: >> I see current criteria and all the SQL-standard compliance policy, but >> wouldn't it still make sense to be able to store a date reference, along >> with a time zone reference? >> Wouldn't it be useful, wouldn't it be

Re: [GENERAL] Date with time zone

timestamptz On Sat, Nov 28, 2009 at 7:25 PM, silly wrote: > Speaking of timestamps, I think it would be convenient to have a > single-word alias for "timestamp with time zone". This is the date > type I use almost exclusively and its name is annoyingly big. > > > > On Sat, Nov 28, 2009 at 6:5

Re: [GENERAL] Date with time zone

Speaking of timestamps, I think it would be convenient to have a single-word alias for "timestamp with time zone". This is the date type I use almost exclusively and its name is annoyingly big. On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane wrote: > Eduardo Piombino writes: >> I see current criteri

Re: [GENERAL] Date with time zone

Eduardo Piombino writes: > I see current criteria and all the SQL-standard compliance policy, but > wouldn't it still make sense to be able to store a date reference, along > with a time zone reference? > Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considerin

Re: [GENERAL] Date with time zone

On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: > Hi Adrian, thanks for your answer. > > I see current criteria and all the SQL-standard compliance policy, but > wouldn't it still make sense to be able to store a date reference, along > with a time zone reference? > Wouldn't it be u

Re: [GENERAL] Date with time zone

Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to "Dec

Re: [GENERAL] Date with time zone

On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote: > Hello list, this is my first msg here. I hope this is the correct place for > this subject, I couldn't find any more specific list for this. > > This thought had been bugging me for some time now and I thought it was > time to share

[GENERAL] Date with time zone

Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a "date with time z

[GENERAL] Date Time Arithmetic Speed

I have an application that I am working on that may do some regular, cron generated time date conversions. It would do: DATE + TIME = TIMESTAMP. It would do a LOT of thesebut spread out over every day to keep the load down and allow the server to do it's may job, dish out the results. Wh

Re: [GENERAL] Date math

On Sun, Jun 28, 2009 at 2:52 PM, Joe Conway wrote: > Adam Rich wrote: > >> Hello, >> I have a table with a DATE field "birth_date". The data obviously >> contains various dates in the past, such as 07/04/1970. In my query, I need >> to retrieve the person's "next" birthday. In other words, for

Re: [GENERAL] Date math

Adam Rich wrote: Hello, I have a table with a DATE field "birth_date". The data obviously contains various dates in the past, such as 07/04/1970. In my query, I need to retrieve the person's "next" birthday. In other words, for the example date 07/04/1970, the query should return 07/04/2009

Re: Fwd: [GENERAL] Date math

Adam Rich wrote: > Guy Flaherty wrote: >> >> >> You could use the extract() function to calculate the day of year of the person's birthdate and then check if this number is within today's day of year and range of days you want to check for, for example, today's day of year + 30 days to be withi

Re: Fwd: [GENERAL] Date math

Guy Flaherty wrote: On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich > wrote: Hello, I have a table with a DATE field "birth_date". The data obviously contains various dates in the past, such as 07/04/1970. In my query, I need to retrieve the person's "ne

Fwd: [GENERAL] Date math

On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich wrote: > Hello, > I have a table with a DATE field "birth_date". The data obviously contains > various dates in the past, such as 07/04/1970. In my query, I need to > retrieve the person's "next" birthday. In other words, for the example date > 07/04/

[GENERAL] Date math

Hello, I have a table with a DATE field "birth_date". The data obviously contains various dates in the past, such as 07/04/1970. In my query, I need to retrieve the person's "next" birthday. In other words, for the example date 07/04/1970, the query should return 07/04/2009 for the current

Re: [GENERAL] date ranges in where

On 07/05/2009 12:59, Jasen Betts wrote: > where lastlogin::date between '2009-05-01'::date and '2009-05-02'::date > > If you leave it uncast postgres will probably convert the lastlogin to > a string and produce results other than that desired and proabaly > take longer to do it too. The OP was

  1   2   3   4   >