Re: [SQL] Last day of month
On Thu, Feb 26, 2004 at 03:07:52AM -, Greg Sabino Mullane wrote:
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
> > How to find the last sunday/mon/sat of any given month.
>
> There is probably a smoother way to do it, but here is a
> quick little function to do what you ask. Feed it a date
> and a number, where 0 is Sunday, 1 is Monday, etc.
select date_trunc('month', current_date + '1 month'::interval) - '1 day'::interval +
(((3 - 7 - to_char(date_trunc('month', current_date + '1 month'::interval) -
'1 day'::interval,'D')::int) %7)||' days')::interval;
The "3" is the day of week (1 = Sunday, 7 = Saturday). This equation
will return the date of the last "x" of the current month. Change
"current_date" to be whatever date you wish to find the last "x" of.
Michael
--
Michael Darrin Chaney
[EMAIL PROTECTED]
http://www.michaelchaney.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres DB
On Mon, Feb 23, 2004 at 02:24:32PM +0530, Sumita Biswas wrote: > The following Query string , when executed does not give an Error, even > though the table Tbl_Billing_Data is not present. > > QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType, > CallManagerId, ClusterId, DestConversationId, ConnectDate, > FinalDestination)''+'' SELECT 2, Global_CallId_CallManagerId, > globalCallId_ClusterID, DestConversationID, StartDate = MIN > (Connect_Date), Final_Destination ''+'' FROM Tbl_Billing_Data''; I had a situation where a subquery had an error in it, and the query simply returned no rows instead of throwing an error. It looks like it might well be a bug that we're experiencing, I just haven't had time to chase it down. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 3: 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: [SQL] User defined types -- Social Security number...
On Sun, Feb 22, 2004 at 04:45:51PM -0800, Greg Patnude wrote: > Thanks Josh -- I understand that there are valid and invalid SSN's -- > similar rules apply to zip codes and area codes... > > I tried this: > > SELECT to_char(123456789, '000-00-'); > which yields 123-45-6789 -- nicely, I might add... > > the trick is getting postgreSQL to do this without having to create an > ON > SELECT and ON UPDATE TRIGGER... > > an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick... > > SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-" > -- > > I do agree that there are valid ranges -- my main concern is being > able to > store any leading zeros - I just need to make sure that something > "looks" > like a valid SSN in the formattig > (nnn-nn-) and that I can store / retrieve it with the approoriate > format -- what I am really trying to accomplish is an "input mask"... Look, you're thinking way too hard on this. An SSN is a 9-digit number, nothing more. There are some 9-digit numbers which aren't valid SSN's, and you might want to get fancy and create a constraint for that. Regardless, you are making a *major* mistake of confusing data storage with rendering. It is common to *render* an SSN as xxx-xx- and its cousin the FETID (Federal Employers Tax ID) as xx-xxx. To store the dashes makes no sense. They're in the same place each time, it's wasted data. Store the SSN as an "integer". When you begin to think about this correctly, the "leading zeros" problem disappears since that is also a *rendering* issue. When you pull the data out, either fix it up in your programming language to the format that you wish, or use the to_char function as shown above in your select statements. To help you think about this whole issue, consider the timestamp datatype. Timestamps are stored as a Julian date internally. I suspect that they use a double-floating point as the actual format, but regardless the point is that it's a number. Rather than storing 2004-02-29 21:14:27.030434-06 We store: 2453065.88503472 It's easier to use that as a basic format from which we can render it in any way we wish. The same applies to your SSN. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] User defined types -- Social Security number...
On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote: > I missed the start of this thread but will chime in with a comment > anyway. > > My rule is to select an appropriate numeric type of data if you will > be doing numeric types of things to it, character types if you will > be doing character manipulations, etc. > > I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN), > SSN+7.86 but there are plenty of good reasons to need the first three > characters (the "area number"), the middle two characters (the "group > number", and the last 4 characters (the "serial number", often > (ab)used as a password for banking and other purposes). Another excellent point. I often store zip codes as text for this reason. The only other thing that I would mention is that if the SSN field in the db will be a key of some sort, which is often the case, then it might be more efficient to store it as an integer. It might be more efficient to store it as a character string. The author should test in this case to determine the most efficient way. As for character vs. integer manipulations, in most scripting style languages, which is pretty much exlusively what I use, there's no need to think about types, and something like an SSN will silently change between being character or integer depending on what operations are being performed on it. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] User defined types -- Social Security number...
On Sun, Feb 29, 2004 at 11:11:31PM -0500, Christopher Browne wrote: > The world rejoiced as [EMAIL PROTECTED] (Michael Chaney) wrote: > > Look, you're thinking way too hard on this. An SSN is a 9-digit number, > > nothing more. There are some 9-digit numbers which aren't valid SSN's, > > and you might want to get fancy and create a constraint for that. > > > > Regardless, you are making a *major* mistake of confusing data > > storage with rendering. It is common to *render* an SSN as > > xxx-xx- and its cousin the FETID (Federal Employers Tax ID) as > > xx-xxx. To store the dashes makes no sense. They're in the > > same place each time, it's wasted data. > > > > Store the SSN as an "integer". When you begin to think about this > > correctly, the "leading zeros" problem disappears since that is also a > > *rendering* issue. > > Well put. > > The one thing that is a bit unfortunate is that 32 bit ints aren't > quite big enough for this. You need 1 extra digit :-(. For what? The largest SSN is 999,999,999, a signed 32-bit int goes to just over 2,000,000,000. Ever hear of a "4GB limit"? Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Convert INT to INTERVAL?
On Fri, Feb 27, 2004 at 11:54:04AM -0500, Brian Knox wrote: > Is it possible within pl/pgsql, to convert an integer to an interval in > months? IE, if num_months is an INT set to 48, can it be converted to an > interval? select (1||' months')::interval; You should be able to replace the "1" with your integer variable. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
