The Heap™ – Consultancy ©® - 2017-02-17 (page 2 of 2)

swasheck

6:11 PM

found a new twitter account to follow

Internet of Shit

@internetofshit, In your stuff

why not put a chip in it. say hello: internetofshit@gmail.com

3.4k tweets, 189k followers, following 152 users

Evan Carroll

@MaxVernon thinking about answering this phone number question

Max Vernon

oh good

Evan Carroll

Yea, I know you want to know THE RIGHT WAY. =/

deep down inside.

this is actually way more complex than the op wants it to be.

I blame him for being in a prison colony.

Max Vernon

I was thinking of updating my answer, but I figure if you're going to post the canonical way of doing it, what's the point.

Evan Carroll

I'm still not sure I can answer this one right

I'm trying now

fucking AU man

I'm not even sure how you do this in AU.

Max Vernon

6:30 PM

dare I ask what AU is?

obviously aside from the periodic table name for gold.

Jack Douglas

astronomical unit?

Evan Carroll

Australia

A horrible continent somewhere on the outskirts of Antarctica.

0

A: SQL: Storing Formatting in Data

PostgreSQL For this, let's use Google's libphonenumber, by proxy of pg-libphonenumber. CREATE EXTENSION pg_libphonenumber; This currently installs the phone_number type which has comparison operators and equivalence testing and the functions. It stores the number in an international canonical...

Max Vernon

but the question only tangentially has anything to do with Australia.

tangentially, as in "only superficially relevant", in case anyone wants to pull apart my use of the term.

that verged on jeagl, but its no joke.

Evan Carroll

If it didn't work at all for Australia, would you still think the answer is valid?

Max Vernon

@EvanCarroll the question is "should I store formatting in data", not "does this work in Australia"

Evan Carroll

6:36 PM

The answer is you should be storing a canonical form of that data whenever possible, the problem with that is it may not work everywhere or even be possible within reason in AU.

Jack Douglas

I don't think you need the leading zero in Australia if you include the country code

Max Vernon

@EvanCarroll so what are you saying, Australia can never use PostgreSQL? I don't follow your reasoning.

Evan Carroll

Fair enough, I didn't know how their pots works.

Max Vernon

@JackDouglas and just because my example uses a bigint doesn't mean it must.

Evan Carroll

@MaxVernon no, I'm saying the Australian phone number system requires a special magic sauce over the international canonicalized version or people in Australia may not be able to use it.

Think about timestamps.

They get stored in UTC.

Max Vernon

6:38 PM

@EvanCarroll oh. That doesn't apply only to Australia. And just what, pray tell, is the "international canonicalized version" of POTS?

Jack Douglas

howtocallabroad.com/australia

@EvanCarroll based on what?

In fact here in Australia mobile phone numbers begin with "04" and the zero is important. If you store that as an INT you will lose that digit. Phone numbers should be strings. — RealityGone 12 hours ago

Evan Carroll

That works because the database can move them out of UTC and make them universally useful to everyone (for most purposes). That's the part that is missing for AU phone numbers. We can store the phone numbers in a canonicallized version so everyone else can make sense of them, but they may not be useful to those inside of AU.

Max Vernon

@EvanCarroll you must be American, right?

Evan Carroll

Are you reading what I AND Jack are telling you?

Or, are you just arguing?

In fact here in Australia mobile phone numbers begin with "04" and the zero is important. If you store that as an INT you will lose that digit. Phone numbers should be strings. — RealityGone 12 hours ago

Max Vernon

of course. It's like that in England and a lot of other places. That doesn't mean you need to store the damn zero.

Jack Douglas

6:40 PM

@EvanCarroll I am saying I don't think Australia is any different to anywhere else and your reading too much into that comment.

You don't need to store the zero

Evan Carroll

I was reading the wikipage, not that comment.

Jack Douglas

@EvanCarroll link?

Evan Carroll

> To access numbers in the same area, it is necessary only to dial the eight digits concerned. To access a number in another "Area" it is necessary firstly to dial the "Trunk Access Code" of 0, followed by the area code (2, 3, 7 or 8) and then the specific "Local" number.

Max Vernon

that would only seem empirically important if you live in an area where everyone thinks they are the center of the universe, and everything works the same way.

Evan Carroll

Telephone numbers in Australia

The Australian telephone numbering plan describes the allocation of phone numbers in Australia. It has changed many times, the most recent major reorganisation by the Australian Communications and Media Authority taking place between 1994 and 1998. == Overview == For landline telephony, Australia is now geographically divided into four large areas, most of which cover more than one State and/or Territory. All "Local" telephone numbers within these four areas are of eight digits, consisting (mainly) of a four digit "Exchange" code plus a four digit number. The "National 'Significant' Numbe...

Jack Douglas

6:42 PM

what in there makes you think Australia is different?

Evan Carroll

So let's say we have Fosters Beerland, and Outback Steak Place. To make a call from one to the other you would NEED the 0 if they're on different trunks. Yet, for me to call them from Oilville Texas, I would NOT NEED the 0.

This isn't the international country code.

Max Vernon

ALSO, THE QUESTION IS ONLY TANGENTIALLY ABOUT PHONE NUMBERS. If you have a good way to solve the problem of storing formatting in the data, then you should answer the question.

@EvanCarroll you seem to think Jack and I have no idea about how to use the phone in "foreign" countries.

Jack Douglas

@EvanCarroll I agree with Max, you don't need to store the zero. "let's say we have Fosters Beerland, and Outback Steak Place. To make a call from one to the other you would NEED the 0 if they're on different trunks" — you could still use the Australian country code from inside Australia and would not need the zero then.

Max Vernon

When I lived in Wales, I could literally call my next door neighbor by picking up the phone and dialing "603". If i wanted to talk to someone a mile away, I needed more digits.

sometimes with a leading zero, and no that's not the damn country code.

Evan Carroll

7 mins ago, by Evan Carroll

Fair enough, I didn't know how their pots works.

Jack Douglas

6:45 PM

pg-libphonenumber: "A (partially implemented!) PostgreSQL extension that provides access to Google's libphonenumber" github.com/blm768/pg-libphonenumber

doesn't sound very production ready

Evan Carroll

Yea, no idea why. It's a 300 line binding to a library. Not sure what he wants out of it. I already filed an issue on that.

I think the reason it's not production ready is the implicit text casting assumes US

and he doesn't want that.

McNets

Why OP change the question when you have supplied a valid answer?

Thank you @McNets , I am still having trouble to convert my actual query. I updated actual query, can you help me with this.

0

Q: count(*) over() MSSQL to Mysql

I want to convert below MSSQL query to Mysql query, especially with ROWNUMBER() and OVER(). Updating actual query. WITH interviewResults AS( Select ROW_NUMBER() OVER(ORDER BY min(T_Interview.ScheduleUtc) desc) as rownum,COUNT(*) over() as totalCount,T_Job.id as jobId, T_application...

mysql

well, I suppose it is valid...

Max Vernon

@McNets that is so annoying.

billinkc

@MaxVernon Good thing they didn't have the 50s era murican phone number system in place. My number? It's Llanfairpwllgwyngyllgogerychwyrndrobwyllllantysiliogogogoch 31246

Max Vernon

lol!

@billinkc and that's an actual place for the rest of you onlookers

billinkc

7:01 PM

Liam Dutton nailing it

►

Max Vernon

oh thats great. A friend of mine used to holiday there, and she can say that damn thing too. It's amazing to hear.

@billinkc mine was slightly easier, at "tintern 606"

billinkc

What was your number back in Morse code days?

Max Vernon

lol

billinkc

<3

McNets

@McNets - Just added some more clarification. We only want the select the rows from which a quantity will be selected. If you want to make those two updates to your answer, ill mark it as the answer. – c_Reg_c_Lark Feb 1 at 20:27

Can you see deleted answers with your reputation level?

Thomas Ward

7:08 PM

@McNets because they don't understand the system heh

McNets

@ThomasWard "If you want to make those two updates to your answer, ill mark it as the answer."

ypercubeᵀᴹ

7:36 PM

@JackDouglas Yes.

But you would want to store that 0 somewhere.

I mean that 0 is the digit required to call other areas from an area in Australia

and 00 if you want to call another country

but 99 if you are in Spain

or whatever ...

and - in a few years - 000 to call the Mars colony

Joe Obbish

is there a good reason to store a phone as an int?

not like you're going to take the average phone number

ypercubeᵀᴹ

I don't see any

Int can't distinguish 673 from 0673

Max Vernon

@JoeObbish perhaps storage size

ypercubeᵀᴹ

it can't store 673AB

Jack Douglas

@ypercubeᵀᴹ do you like my logo?

ypercubeᵀᴹ

7:40 PM

@JackDouglas yey !

thanks :)

yes, its trés cool

merci!

@MaxVernon I've had the luxury of never needing to resort to tricks like that. I wonder how well phone numbers would compress

ypercubeᵀᴹ

I think Facebook was storing phone numbers as int or bigint.

Max Vernon

7:54 PM

@JoeObbish that would make for an interesting blog post perhaps.

billinkc

No, don't you ever store a phone number as anything but text

Max Vernon

that sounds like you've had an interesting experience.

billinkc

Whatever storage cost you're going to save is going to be dwarfed but all the times someone screws up reconstituting the phone number with formatting

When I used to send you junk mail, we were a rollodex as a service provider. The databases that stored phones or postal codes as ints were always troublesome

When our clients' customers were only US, all was well, but get this: Canadian zip codes have characters in them

Max Vernon

lol. yes they do.

billinkc

Or the knuckleheads who explicitly define the size as char(5) and then we get Canadian postal codes of A5X5J or was it 5X3J4

Max Vernon

8:03 PM

A1B 2C3

the space is accepted as important in lots of places, although it actually isn't

billinkc

Either way, just because it appears to be numeric unless you're doing math with it, as Obbish points out, don't store it as a numeric type

Max Vernon

more precisely, store data using the correct type for the domain

billinkc

Yeah, but when people figure out they have 7 characters to stuff into a 5 character field, they think they can get away with only losing "1" of them

@MaxVernon Preach!

Max Vernon

@billinkc lol. But seriously, as you know, it's important to figure that stuff out properly. Under no circumstances shalt thou store dates in varchar columns.

Andriy M

8:19 PM

No-one seems to have ever suggested storing phone numbers as dates, for some reason. It's true that you would have limitations with dates sand some numbers are just impossible to be stored that way. However, only think that you would always get them formatted in the output!

ypercubeᵀᴹ

call me at 24-july-2017

Max Vernon

lol

ypercubeᵀᴹ

Oh, he got suspended dba.stackexchange.com/users/117161/matthew-sontum

Hadn't noticed that.

Andriy M

Yep:

21 hours ago, by Evan Carroll

whelp he got his first suspension.

ypercubeᵀᴹ

I like the subtle prediction that it won't be the last one ;)

Max Vernon

8:43 PM

@ypercubeᵀᴹ perhaps not prediction as much as assumption from his own experience?

billinkc

9:13 PM

I'm still thinking about how I can slip the usage of date type in for a phone number. I'd be dailywtf famous

Max Vernon

Why does Excel hate me? Whenever I paste in a date from SSMS it only shows me the time component. Every time, I have to change the format of those cells to yyy-mm-dd

every.damn.time.

oh, and it's Stone-Age Excel by the way. '07