Re: [GENERAL] Partial dates
On Wed, Sep 14, 2005 at 00:09:58 -0400, Joe [EMAIL PROTECTED] wrote: I'm not sure I agree with the need to fix or sanitize the data. The columns in question are used mostly for publication dates. While you may be able to find a full release date for recent books, they are generally listed as published on a given year, period. Most monthly magazines only have a month-year of publication. And of course, daily newspapers and Internet articles usually have a full day-month-year. In fact, the MySQL solution didn't address quarterly or bi-monthly publications as that data was only captured as one of the months in the period--as opposed to Mar/Apr 2005 or First Quarter 2005 (or worse: Winter 2004). As Tom Lane argued, it seems I'll have to bite the bullet and create a new datatype. The only other alternative I see would be to split the publication date into three columns and that's rather ugly. It seems like in your case the dates are best expressed as ranges and that you could store the information you have using an earliest possible date and a lastest possible date. If there are extra constraints based on how far apart the begin and end dates are you could implement them as well. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Partial dates
On Wed, 2005-09-14 at 15:49 +1200, Brent Wood wrote: Sanitizing is one thing, inventing data to fit an incomplete value into a date datatype is not good practice. Choose another datatype, or make a new one, or split the date into columns. The type of your data is not a timestamp nor a date. In the first place, 0 is not NULL. So, even 1980-01-00 would be different from 1980-01-NULL. For example, assuming 1980-01-00 is defined to have some meaning, (1980-01-00 1980-01-02) is likely to be true, but (1980-01-NULL 1980-01-02) definitely is not. You're just asking if there's a way to store a number of which the lower bits are ignored (considered NULL). Obviously, no, you need a different datatype or a different arrangement. Note: the string 1980-01-00 just *looks* like a possible value, but definitely it's not: there's simply no space (or time) between 1979-12-31 and 1980-01-01. It's much like trying to store sqrt(-1) into a real. I hardly can imagine how MySQL manages to store that (the 1980-01-00, I mean). .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Partial dates
On Tue, 13 Sep 2005, Joshua D. Drake wrote: ERROR: date/time field value out of range: 1997-10-00 HINT: Perhaps you need a different datestyle setting. CONTEXT: COPY Entry, line 1, column EntryDate: 1997-10-00 Well the easy solution is to just make the date a text type but that is the wrong answer. The right answer is to fix the data set. MySQL should never have allowed you do insert those dates in the first place. I know that doesn't help you much but at some point you are going to have to sanitize the data anyway. Hmmm... given that our real world data, (currently in a commercial RDBMS but I have hopes :-) often has dates where we only have a month and year, is there any way a part of a timestamp can be null? I guess this also has indexing issues. Maybe some form of GIST would work. Sanitizing is one thing, inventing data to fit an incomplete value into a date datatype is not good practice. It would need some arbitrary standard to apply date/time arithmetic queries. For example, if we wanted all values for 1987, a record from an unknown day in March 1987 would be in the result set. If we wanted all values from March 1987, similarly. All records since 13 March 1987 and the arbitrary rule would come into play. Probably excluded because we couldn't explicitly prove it should be included in the result set. Like other nulls get treated. In case anyone is interested, right now we store year, month day and have a timestamp field where the entire field is null if any one part is unknown. Are there any better ways in Postgres? Brent Wood ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Partial dates
On Wed, Sep 14, 2005 at 03:49:29PM +1200, Brent Wood wrote: In case anyone is interested, right now we store year, month day and have a timestamp field where the entire field is null if any one part is unknown. Are there any better ways in Postgres? You can create a new type, which is a privilege you don't have in some other systems. Or you can separate the column in three columns and leave some of them NULL as appropiate. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Right now the sectors on the hard disk run clockwise, but I heard a rumor that you can squeeze 0.2% more throughput by running them counterclockwise. It's worth the effort. Recommended. (Gerry Pourwelle) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Partial dates
I'm converting a MySQL database to PostgreSQL. Two of the tables have DATE columns which have many partial dates. For example, a partial date may be for the publication date of a book, where the date is specified as only the year, e.g., 1957-00-00, and another partial date may be the publication date of a periodical specified as a month and year, e.g., 2005-03-00. MySQL accepts these apparently invalid or incomplete dates, but when I try to copy them into Postgres, I get errors like ERROR: date/time field value out of range: 1997-10-00 HINT: Perhaps you need a different datestyle setting. CONTEXT: COPY Entry, line 1, column EntryDate: 1997-10-00 I read Appendix B and section 8.5, but I didn't find any way around this, i.e., it seems Postgres insists on complete dates with no zero day of month or month. Changing the zeros to ones would be major editing task and the application code would still have trouble distinguishing whether 2005-03-01 meant March 2005 (a monthly publication date) or 1st March 2005 (a date of an article published on that date or of a weekly periodical) (because right now it interprets the zero day of month as the former). Any suggestions (aside from designing a new datatype)? Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partial dates
ERROR: date/time field value out of range: 1997-10-00 HINT: Perhaps you need a different datestyle setting. CONTEXT: COPY Entry, line 1, column EntryDate: 1997-10-00 I read Appendix B and section 8.5, but I didn't find any way around this, i.e., it seems Postgres insists on complete dates with no zero day of month or month. Changing the zeros to ones would be major editing task and the application code would still have trouble distinguishing whether 2005-03-01 meant March 2005 (a monthly publication date) or 1st March 2005 (a date of an article published on that date or of a weekly periodical) (because right now it interprets the zero day of month as the former). Any suggestions (aside from designing a new datatype)? Well the easy solution is to just make the date a text type but that is the wrong answer. The right answer is to fix the data set. MySQL should never have allowed you do insert those dates in the first place. I know that doesn't help you much but at some point you are going to have to sanitize the data anyway. Sincerely, Joshua D. Drake Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Partial dates
Joshua D. Drake [EMAIL PROTECTED] writes: Any suggestions (aside from designing a new datatype)? Well the easy solution is to just make the date a text type but that is the wrong answer. The right answer is to fix the data set. MySQL should never have allowed you do insert those dates in the first place. I think the right answer is that a new datatype is exactly what you need. SQL-spec dates are absolutely *not* meant to express these concepts (and the fact that MySQL allows you to insert dates with zero fields just shows what a lack of error checking they have). It's really not that hard to build a new datatype in PG, especially if you only need it to store values and not do any particularly amazing computational feats. Consider for instance a domain over type text with appropriate constraint checks. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Partial dates
Joshua D. Drake wrote: Well the easy solution is to just make the date a text type but that is the wrong answer. The right answer is to fix the data set. MySQL should never have allowed you do insert those dates in the first place. I know that doesn't help you much but at some point you are going to have to sanitize the data anyway. I'm not sure I agree with the need to fix or sanitize the data. The columns in question are used mostly for publication dates. While you may be able to find a full release date for recent books, they are generally listed as published on a given year, period. Most monthly magazines only have a month-year of publication. And of course, daily newspapers and Internet articles usually have a full day-month-year. In fact, the MySQL solution didn't address quarterly or bi-monthly publications as that data was only captured as one of the months in the period--as opposed to Mar/Apr 2005 or First Quarter 2005 (or worse: Winter 2004). As Tom Lane argued, it seems I'll have to bite the bullet and create a new datatype. The only other alternative I see would be to split the publication date into three columns and that's rather ugly. Thanks for the feedback. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partial dates
On 9/14/05, Joe [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: Well the easy solution is to just make the date a text type but that is the wrong answer. The right answer is to fix the data set. MySQL should never have allowed you do insert those dates in the first place. I know that doesn't help you much but at some point you are going to have to sanitize the data anyway.I'm not sure I agree with the need to fix or sanitize the data.The columns in question are used mostly for publication dates.While you may be able tofind a full release date for recent books, they are generally listed aspublished on a given year, period.Most monthly magazines only have a month-year of publication.And of course, daily newspapers and Internetarticles usually have a full day-month-year.In fact, the MySQL solution didn'taddress quarterly or bi-monthly publications as that data was only captured as one of the months in the period--as opposed to Mar/Apr 2005 or First Quarter2005 (or worse: Winter 2004). As Tom Lane argued, it seems I'll have to bite thebullet and create a new datatype.The only other alternative I see would be to split the publication date into three columns and that's rather ugly.Thanks for the feedback.Joe---(end of broadcast)---TIP 6: explain analyze is your friend probably you can add a new column to identify the publication type and sanitize the wrong dates ( like 2005-00-00 to 2005-01-01). Now based on the publication type you can extract the portion of the date you want.-- with regards,S.GnanavelSatyam Computer Services Ltd.
