No, my understanding is that the MAC address is always visible, even on wifi networks in WPA/WPA2 personal or enterprise mode.
Also, I believe modern phones randomize their MACs when they scan for networks, but use their real MAC when they connect, and that's visible to anyone within listening range.
Btw, most "guest" wifi networks rely on MAC-based access control, using these same publicly visible MAC addresses.. it's an inherent weakness of the wifi standards and I think the main reason why devices can't randomize their MACs when actually connecting.
Yes, that is true, for modern devices that randomize their MAC addresses. I wasn't up to speed on that. But at least you should be able to see that a device was newly connected to the network. (Due to increased traffic between the two.) If the home WiFi does not have very many devices coming and going, you could probably do pattern matching and learn to fingerprint individual devices. At the very least, you should be able to see that "someone is home".
According to someone at DerbyCon (I know who, but don't have permission to say who), they made the claim that Bluetooth is always on and does not randomize the management frames. And that when phones turn off Bluetooth, they actually just turn off data comms from the BT chip TO the mobile CPU.
Their claim was that BT still responds to mgmt frames even when off, doesnt randomize MAC addresses, and some data can even be sent that will then turn on when the BT is 'turned on'.
I saw demonstrations of it. It, well, scared and awed me.
> Also any sane person would never sort random values.
I think sorting by a arbitrary, unique value as the last sorting dimension is useful to ensure you always get the same ordering when first dimensions are equal. For instance sorting users by (last_name, first_name, user_id) ensures homonymous users don't move around in the list from request to request.
This is quite a sane thing to do, don't you think?
Yes, I do that all the time. Even for tables that have a sequence column to indicate the desired display order, I'll add the primary key on the end of the order by to ensure consistency just on the off chance of two rows having the same seq value.
> Postgres has two types of timestamps. It has a generic timestamp and one with timezone embedded in it.
That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a displayable string, so timestamptz are always displayed with a timezone, but that info does not come from the stored value.
Timestamptz needs timezone information only for operations that would give different results in different timezones, e.g. display as string, extract the day part, add a 1-month interval (DST info needed), etc. Comparing two timestamptz however doesn't require any timezone info.
The difference between timestamp and timestamptz is not about what they store, but about how they behave.
Edit: In my experience, this is not always obvious because postgres uses the default timezone of the instance whenever it needs such info with timestamptz operations. Using an explicit timezone often requires convoluted code.
Well I would use timestamptz, using user's timezone only to convert for display. Use cases for timestamp are very limited.
Just make sure you include a timezone info in string representations in your SQL queries. For example '2000-01-01T00:00:00Z' where Z stands for UTC. Otherwise that would insert a timestamp into a timestamptz column, in which case postgres uses local timezone setting for conversion, implicitly; this is not what you want.
Also you should use an equivalent type in you app, i.e. python datetime with tzinfo or JS Date. And beware of UTC offsets: they can't handle DST. Python pytz and JS moment-timezone provide DST-aware timezone info (which is built-in in postgres).
Edit: if you can rely on your users system time for display that's even better because you wouldn't have to explicitly deal with those DST-aware timezone info.
