8 ms·

A developer joined a team I was on once. The boss was infamous for hiring anyone with a pulse and assigning a small client project. Sink or swim. As the senior

by zikzak 6y ago

A developer joined a team I was on once. The boss was infamous for hiring anyone with a pulse and assigning a small client project. Sink or swim. As the senior dev it was my job to provide info as needed and subtly assess the new, potential rockstar code ninjas. Anyway, this new guy says "I store all my dates in sql as text in iso format so I can easily sort them".

com2kid 6y ago
That is... almost not horrible? If he had said anything other than "in sql" it would be the correct answer. "I store all dates in spreadsheets in ISO format" "I prepend ISO dates to marketing campaign names so I can easily sort them in the horrible web frontends that doesn't have a sort by date option" "I start all my blog entries with ISO dates..." I mean, just... so close!
- collyw 6y ago
  It's actually a decent hiring process providing the new hires are made aware of it in advance.
  - zikzak 6y ago
    He was a radical honestly type guy. It was all on the table and we had lots of success stories. We would give anyone a chance which let a lot of self taught, entry level people get work. I got a ton of experience mentoring people, as well (difficult people, too). Unfortunately, he got screwed over by a shotgun clause in a business agreement and lost the business that to an ex-partner. That's what led me to my current job, though, where I've been happy for over 10 years and it's much more stable.
- dragonwriter 6y ago
  > "I store all dates in spreadsheets in ISO format" As in SQL, the application’s native date format (which is usually a number with appropriate format) would be the natural choice and a YYYY-MM-DD string would be a distant second. ISO (presumably, 8601) format is too broad, and admits of a mix of formats that are not simply sortable (quick, which is first 2021-W14-2 or 2021-04-07?).
  - com2kid 6y ago
    Eh, when people say ISO they mostly mean YYYY-MM-DD. If a date is going to be string prepended to something else, or if the spreadsheet is going to be exported to CSV, or parsed by some tool, then storing dates in YYYY-MM-DD can be a very justifiable thing to do in a spreadsheet. Also Excel likes to format dates using the system locale, which is, well, stupid. So I have to enter YYYY-MM-DD as a custom format anyway, or change the locale on the column to China. (Or Cherokee, who apparently also properly format their dates!) (Also Excel is smart enough if you set a column as YYYY-MM-DD custom format, it'll let you use all the date based pivot table stuff as expected)
qmmmur 6y ago
Why is this bad?
- happythought 6y ago
  Sql databases usually come with their own date types that are implemented with integers behind the curtains. They take up less space and are easier to sort than text fields. EDIT: also I wouldn’t consider this egregious. If the Senior explains and the person was happy to learn something then that’s a good outcome. If they are stubborn about it, then that wouldn’t be great.
  - deathanatos 6y ago
    But which very likely store less information than an ISO timestamp does. (The integers stored are usually POSIX timestamps.) So, you might have to store, e.g., timezone information elsewhere. (And that might also be a good thing. It might not. It depends. Without knowing more, the DB's type is probably the right choice.) I've seen far worse things than this done to a SQL database though…
    - zachrose 6y ago
      Except that ISO-8601 doesn’t include anything as useful as a political time zone, just an offset from UTC. That’d be good if UTC offset was useful for anything, but instead it just adds to the confusion.
      - chillfox 6y ago
        UTC offset is way more useful than those 3-4 character timezone abbreviations. Just have a look at the list from Wikipedia [1] and notice how many repeats there are. As an example AMT could be either UTC+4 or UTC-4. And this doesn't even includes the various local acronyms that people use around the world. [1] https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations https://en.wikipedia.org/wiki/List_of_time_zone_abbreviation...
pcr910303 6y ago
> I store all my dates in sql as text in iso format so I can easily sort them Strong SQLite vibes... (SQLite doesn't have a date format, it has builtin functions that accept ISO format text or unix timestamps)
- calvinmorrison 6y ago
  As a Unix person, aren't dates just number? Why is sort complex? Just parse input and render timestamps
  - adrianN 6y ago
    Time zone information makes this a bit tricky.
  - yen223 6y ago
    It's pretty miserable when you're trying to look up a date, and all you get are numbers that requires a conversion step to be readable.
  - drchickensalad 6y ago
    Assuming by number you mean epoch seconds/milliseconds, a number can store the instant in time, but not a TIMEZONE. Obviously you could do bit magic if you shift it over (so you don't mess up sorting), but that's true for any data and a sufficiently large number type.
- zikzak 6y ago
  That's a good point. We were talking MS SQL but yes, that would make way more sense.
dmingod666 6y ago
I once saw an app that had a text column as date, then 3 other columns for day month and year so they could sort by date.
- arethuza 6y ago
  I remember one design I reviewed that wanted to use XML as something to be queried using simple text comparison (this was before some databases adding explicit support for XML).
  - dmingod666 6y ago
    Not XML, but, the classic 'never parse html with regex' https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags https://stackoverflow.com/questions/1732348/regex-match-open... :)
chaps 6y ago
Meh, I don't see much wrong with this honestly. There are some cases where using text as the type is the only way. Bet you the junior dev worked with a lot of messy data in the past, where it wasn't always possible to do a direct datestr -> SQL date type. Just point him in the right direction and move on.
- zikzak 6y ago
  Yeah, I did explain why that wasn't "how we did it", and tried not to sound like a know it all (I am definitely not this). I am way less judgemental now and a lot of the comments here are making me rethink what I thought about him saying this. He had a lot of other issues and resigned after a couple of days so I guess we'll never know what he was capable of.
  - chaps 6y ago
    Hah, amazing and thanks for sharing. I think it's mostly that you sounded like you were holding a petty grudge over some small technical thing that can make sense. Maybe he resigned because he felt overly judged?
    - zikzak 6y ago
      No, he had a day drinking and absentee problem. My boss talked with him, offered him another chance, but he resigned. He really didn't like following requirements either but I think it was just a symptom of his overall problems. He was going through something and we were just a stop on his journey.
      - chaps 6y ago
        Fair enough. Cheers.
lmm 6y ago
Sounds like someone who's realised how crappy most databases' time handling is.
beagle3 6y ago
I did that in DB2 or Oracle (or both, don’t remember - it was 15 years ago and the project used both) because I needed millisecond time resolution and the database date time field did not support milliseconds. Among the option of storing (date field, millisecond field) and (date as iso8601 text field with millisecond without time zone) I chose the latter, and I can’t tell for sure but it was almost surely the better choice.
- hnarn 6y ago
  Surely you can do both, so you can still perform sane sorting/indexing on "real" timestamps and then use the extra precision string timestamp for drilling down?
  - beagle3 6y ago
    Iso8601 sorts just as well. I saw no benefit in storing two copies. That’s recipe for problems - e.g. “update” code that doesn’t update both, or somehow updates them differently (which one is right?). You can probably have t-sql or some “check” constraint to enforce equivalence, but ... why? For most practical uses, an ISO8601 textual representation is as good, though a little less space efficient.
    - dragonwriter 6y ago
      > Iso8601 sorts just as well. No, it doesn’t, since all of the following are valid ISO 8601 representations of a date: 20210408 2021-04-07 2021-W14-1 2021W143 2021-097 2021098 (Now, consistently using any one of the ISO 8601 formats is sortable...)
      - beagle3 6y ago
        Point taken. I always recommend and use the specific ISO8601 format YYYY-MM-DDThh:mm:ss.sssZ (or drop the ".sss" if not needed), and be 100% consistent about it. But you are right, I shouldn't refer to this as "use ISO8601" because it is only one of the numeous possible formats described by that spec.

by zikzak 6y ago

com2kid 6y ago

That is... almost not horrible? If he had said anything other than "in sql" it would be the correct answer. "I store all dates in spreadsheets in ISO format" "I prepend ISO dates to marketing campaign names so I can easily sort them in the horrible web frontends that doesn't have a sort by date option" "I start all my blog entries with ISO dates..." I mean, just... so close!

collyw 6y ago
It's actually a decent hiring process providing the new hires are made aware of it in advance.
- zikzak 6y ago
  He was a radical honestly type guy. It was all on the table and we had lots of success stories. We would give anyone a chance which let a lot of self taught, entry level people get work. I got a ton of experience mentoring people, as well (difficult people, too). Unfortunately, he got screwed over by a shotgun clause in a business agreement and lost the business that to an ex-partner. That's what led me to my current job, though, where I've been happy for over 10 years and it's much more stable.
dragonwriter 6y ago
> "I store all dates in spreadsheets in ISO format" As in SQL, the application’s native date format (which is usually a number with appropriate format) would be the natural choice and a YYYY-MM-DD string would be a distant second. ISO (presumably, 8601) format is too broad, and admits of a mix of formats that are not simply sortable (quick, which is first 2021-W14-2 or 2021-04-07?).
- com2kid 6y ago
  Eh, when people say ISO they mostly mean YYYY-MM-DD. If a date is going to be string prepended to something else, or if the spreadsheet is going to be exported to CSV, or parsed by some tool, then storing dates in YYYY-MM-DD can be a very justifiable thing to do in a spreadsheet. Also Excel likes to format dates using the system locale, which is, well, stupid. So I have to enter YYYY-MM-DD as a custom format anyway, or change the locale on the column to China. (Or Cherokee, who apparently also properly format their dates!) (Also Excel is smart enough if you set a column as YYYY-MM-DD custom format, it'll let you use all the date based pivot table stuff as expected)

qmmmur 6y ago

Why is this bad?

happythought 6y ago
Sql databases usually come with their own date types that are implemented with integers behind the curtains. They take up less space and are easier to sort than text fields. EDIT: also I wouldn’t consider this egregious. If the Senior explains and the person was happy to learn something then that’s a good outcome. If they are stubborn about it, then that wouldn’t be great.
- deathanatos 6y ago
  But which very likely store less information than an ISO timestamp does. (The integers stored are usually POSIX timestamps.) So, you might have to store, e.g., timezone information elsewhere. (And that might also be a good thing. It might not. It depends. Without knowing more, the DB's type is probably the right choice.) I've seen far worse things than this done to a SQL database though…
  - zachrose 6y ago
    Except that ISO-8601 doesn’t include anything as useful as a political time zone, just an offset from UTC. That’d be good if UTC offset was useful for anything, but instead it just adds to the confusion.
    - chillfox 6y ago
      UTC offset is way more useful than those 3-4 character timezone abbreviations. Just have a look at the list from Wikipedia [1] and notice how many repeats there are. As an example AMT could be either UTC+4 or UTC-4. And this doesn't even includes the various local acronyms that people use around the world. [1] https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations https://en.wikipedia.org/wiki/List_of_time_zone_abbreviation...

pcr910303 6y ago

> I store all my dates in sql as text in iso format so I can easily sort them Strong SQLite vibes... (SQLite doesn't have a date format, it has builtin functions that accept ISO format text or unix timestamps)

calvinmorrison 6y ago
As a Unix person, aren't dates just number? Why is sort complex? Just parse input and render timestamps
- adrianN 6y ago
  Time zone information makes this a bit tricky.
- yen223 6y ago
  It's pretty miserable when you're trying to look up a date, and all you get are numbers that requires a conversion step to be readable.
- drchickensalad 6y ago
  Assuming by number you mean epoch seconds/milliseconds, a number can store the instant in time, but not a TIMEZONE. Obviously you could do bit magic if you shift it over (so you don't mess up sorting), but that's true for any data and a sufficiently large number type.
zikzak 6y ago
That's a good point. We were talking MS SQL but yes, that would make way more sense.

dmingod666 6y ago

I once saw an app that had a text column as date, then 3 other columns for day month and year so they could sort by date.

arethuza 6y ago
I remember one design I reviewed that wanted to use XML as something to be queried using simple text comparison (this was before some databases adding explicit support for XML).
- dmingod666 6y ago
  Not XML, but, the classic 'never parse html with regex' https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags https://stackoverflow.com/questions/1732348/regex-match-open... :)

chaps 6y ago

Meh, I don't see much wrong with this honestly. There are some cases where using text as the type is the only way. Bet you the junior dev worked with a lot of messy data in the past, where it wasn't always possible to do a direct datestr -> SQL date type. Just point him in the right direction and move on.

zikzak 6y ago
Yeah, I did explain why that wasn't "how we did it", and tried not to sound like a know it all (I am definitely not this). I am way less judgemental now and a lot of the comments here are making me rethink what I thought about him saying this. He had a lot of other issues and resigned after a couple of days so I guess we'll never know what he was capable of.
- chaps 6y ago
  Hah, amazing and thanks for sharing. I think it's mostly that you sounded like you were holding a petty grudge over some small technical thing that can make sense. Maybe he resigned because he felt overly judged?
  - zikzak 6y ago
    No, he had a day drinking and absentee problem. My boss talked with him, offered him another chance, but he resigned. He really didn't like following requirements either but I think it was just a symptom of his overall problems. He was going through something and we were just a stop on his journey.
    - chaps 6y ago
      Fair enough. Cheers.

lmm 6y ago

Sounds like someone who's realised how crappy most databases' time handling is.

beagle3 6y ago

I did that in DB2 or Oracle (or both, don’t remember - it was 15 years ago and the project used both) because I needed millisecond time resolution and the database date time field did not support milliseconds. Among the option of storing (date field, millisecond field) and (date as iso8601 text field with millisecond without time zone) I chose the latter, and I can’t tell for sure but it was almost surely the better choice.

hnarn 6y ago
Surely you can do both, so you can still perform sane sorting/indexing on "real" timestamps and then use the extra precision string timestamp for drilling down?
- beagle3 6y ago
  Iso8601 sorts just as well. I saw no benefit in storing two copies. That’s recipe for problems - e.g. “update” code that doesn’t update both, or somehow updates them differently (which one is right?). You can probably have t-sql or some “check” constraint to enforce equivalence, but ... why? For most practical uses, an ISO8601 textual representation is as good, though a little less space efficient.
  - dragonwriter 6y ago
    > Iso8601 sorts just as well. No, it doesn’t, since all of the following are valid ISO 8601 representations of a date: 20210408 2021-04-07 2021-W14-1 2021W143 2021-097 2021098 (Now, consistently using any one of the ISO 8601 formats is sortable...)
    - beagle3 6y ago
      Point taken. I always recommend and use the specific ISO8601 format YYYY-MM-DDThh:mm:ss.sssZ (or drop the ".sss" if not needed), and be 100% consistent about it. But you are right, I shouldn't refer to this as "use ISO8601" because it is only one of the numeous possible formats described by that spec.