- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
I HAVE ACHIEVED SATORI
Admin
I know I will be laughed at, but I have long since decided that using any Date format in DB's gives me more trouble than it's worth and I simply use a Number containing millis since 1970. Surprisingly little conversion has to be done.
Admin
It seems like every DBMS has their own quirks with handling dates. I think it's worth learning these quirks in order to take advantage of the cool built in date functionality.
I wouldn't be surprised if the dates are actually stored based on the scheme you use. It would drive me nuts not to have a "meaningful" looking date when I am scrolling through a table.
Admin
That is actually related to one of my favorite WTF's I've ever seen: somebody did what you described, and stored it in a column with type NUMERIC(12).
Of course, 1,000,000,000,000 milliseconds after 1/1/1970 turned out to be only a couple of years after the application was released. You'd think someone what have suspected something was up when they noticed that all the timestamps were things like 987654321098.
Admin
Admin
So upgrade MySQL to a newer version, refactor the tables to use the correct type, and change the SQL in the app.
A WTF but hardly one that's in any way difficult to deal with.
Big deal.
Admin
Upgrading is easier said than done in the real world. Management types are usually very paranoid about something, ANYTHING going wrong and breaking during the upgrade, so they'll mandate the use of the old, clunky version since "It works", although our ("our" meaning developers) definition of "works" differs from theirs.
I once worked for a company that happily was using Foxpro 2.6 (the Windows 3.1 version, although at least two machines were using DOS interfaces) and some ancient programs written over 10 years ago in Delphi 2 or 3, and had no plans to ever upgrade because they were too afraid of something not working, and because the original developer specifically made a core application dependent on manually loading a DBF file.
Admin
For reasons that I assume must be related to query optimization, none of Oracle, MS-SQL, or MySql actually stores dates in a simple way such as that.
For example.
Admin
Just change the system (e.g., in a testbed) to use the correct date type, and then convert the damn database tables and have it over with. Annoying, yes, but hardly "not feasible".
Admin
I have to admit that if I was facing this I would put the query results in an ArrayList, convert the strings to Dates, and do the sorting in memory. Yes, the database would still be designed in a fucked up way. Yes, it would be slightly less efficient. But it would get me all kinds of praise for having done the "infeasible".
Of course I'm thinking in Java. Maybe this truly is infeasible in PHP?
Admin
I agree with Bejesus. A WTF yes, but one easily solved. Even if they refuse to upgrade MySQL, the strtotime function in php should make short work of it. All you need is to grab every record in the table then parse with php. To get that "Working" (although possibly slowly depending on the size of the table), would take maybe 10-20 minutes. Making it fast(er) would probably take longer, but a 20 minute task certainly feel feasible to me.
I suspect other reasons for not renewing the contract....
Admin
Oh trust me it isn't. The reason everyone mocks php is because any idiot can write in it, and make things happen. So no need to deal with all of those tricky java things like... Types.
Admin
Wait... If the "most recent" contractor declared it unfeasible, then how could "another" contractor get it done? :P
Admin
Admin
i've seen a lot of schemas where all columns are varchars, sometimes id is an integer. most developers say they hate the old school rdbms. but they are not using them well :((
btw: select * from yourtable order by to_date(date_col,'MON YYYY HH24...');
Admin
Hey, Mr. Prima Donna, good luck with finding that magical place where all the legacy code is perfect.
Admin
Admin
Hopefully for that place, there just might be a simple way to transition over (maybe modifying whatever it is that is repopulates the MySQL database from Oracle periodically to also update a new column with a proper date datatype).
Such is a standard part of the challenges in working in IT. Hopefully one could layout the arguments and reasons both in technical speak and management/layman's terms behind the change, and pass it to management. They may not agree to it, but at least they will hopefully "see" the reasons.
Especially since in all reality they will probably go through many more contractors, the far majority of which will probably never communicate the problems with their stuff and do what most everyone else does, which is cut and run after their contract is up.
I've done it many a time, like probably all of us. Then again, when I feel like management is understanding and capable of listening and agreeing to change (like my current employ), I've settled and enjoyed working through such joyous nightmares. It's great to see the users go from processes that take minutes to run to only a few seconds - they're simply amazed :)
Admin
Interesting.
SQL Server does this: (From BOL)
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
Not so handy for library or museum apps... ;)
Captcha = damnum ! lol.
Admin
Upgrading for large companies is difficult. Depending on how "Proven" the system is.
Sure you can preach: Upgrading to version XX from AA will give performance increase, scalability, necessary functionality, fix stability issues, etc. etc. etc...
However what currently exists has been tested. And "proven" to at least work correctly (if it does). Now if you want to upgrade it means that you must do the following:
The problem is steps 3 and 4 could be quite costly.
On another note, when any programmer works for you, you need the following:
Admin
Admin
The real WTF is that their version of MySQL did not support user-defined functions as well.
Admin
It says that the entire database is "repopulated" from an oracle database every day. Doesn't that mean that if you write the correct 'conversion' function (and place it in a new column) you can have the new format converted automatically in one day.
After that you refactor all code (starting with the sort courses) while the live database holds two columns of dates (one old style, one new style) and finally you throw away the old column.
Admin
I work at a huge web portal corporation, and here it really is not feasible. Just as keeping developers to maintain "everything".
We have hundreds upon hundreds of services. Nobody exactly knows how many. A team of 3-4 people makes 3-4 new services yearly, using a quite comfortable and efficient backend API that interfaces to some antiquated MySQL. Thing is, the service is introduced, initial changes are made, the Bathtub Curve goes down, and we move on to the next service. Unless it's a 'flagship product', nobody bothers to learn all the services and their caveats when another employee quits, nobody really maintains them. They live their life for years unchanged.
It's not a fuckup/WTF situation, it's a fully conscious strategy. If a team of 4 people had to maintain 15 services they had produced over the last 5 years, they wouldn't have time to do any more work. They are left unmaintained in full awareness of the consequences, and if ever need arises for a modification, the team designated first researches the service (digging in old documentation, reading the code), then introduces the changes, then leaves it again. If the changes are to be big, the service is written from scratch. And it IS feasible. The cycle of research-modify-forget is cheaper than keeping track of every single service and teaching it all to the new crew as the old goes for greener pastures, because the changes and errors are rather rare, and small errors are just allowed to be, sometimes for years.
Yeah, it pays to do things like that. You need to double the resources to halve the amount of errors. So getting from 80% efficiency to 90% efficiency costs the same as getting from 99% to 99.5%. We're much better off leaving stuff working at 90% and moving on to another project, than getting it to work 99.99%. The quality of software is barely acceptable, but the amount of content is staggering and that's what gets us lots of customers.
The caveat is that the backend's API can't be modified, ever. At best extended. At any point there's a hundred of services that would break the moment some function changes, and nobody knows what they are. There are pages that would fail and remain broken for weeks before anyone notices they are broken. Nobody is willing to pay for fixing them and it's just cheaper to write things from scratch. So - no backend upgrade, sorry.
You may think this is a horrible code management, horrible maintenance procedures and generally bad tactics - such chaos, disorder, unmaintainablity. The trick is, that it pays, and it pays well, better than well-managed code. Management costs. Maintenance costs. Design fuck-ups don't save you from rewriting large sections of even well-managed code. Keeping everything orderly and tidy costs time and money, whether you work on it or not. Documenting every single function, whether it is to be ever used by someone or not often takes more time than writing it from scratch. The chaos comes at a price too. You spend time decoding others' code, refactoring is a slow, dangerous and difficult work, errors happen, and are expected to happen. A percentage of users get tired with timeouts, errors and so on, and quit. But more users than leave, come to see the new content, cope with the errors to access what no competitor has to offer, use our services because our databases contain more entries and more stuff can be found here than elsewhere.
So, "create a bunch of code, leave it working for 5 years until bit rot kills it, rewrite from scratch when it finally dies" IS more cost-efficient than "create pretty software, maintain and keep improving it, always keeping track of the changes and using best practices at all times". Yeah, sooner or later you WILL need to rewrite your growing app from scratch, no matter how well-written and maintainable and tidy it was, or is. Don't you feel pity for all that pretty code going obsolete? A precious (and expensive) gem going to waste. We don't polish turds. We release what suffices, early and in bulk amounts. If it breaks, oh well, we have two new ones to replace it almost ready. We'll lose 1000 users, gain 3000 new ones. And fixing the old thing would cost about as much as making the two new ones from scratch does.
Admin
Admin
I beg to disagree, upgrades are very easy to justify to management. One use of the word "security" is generally all it takes to get them completely on side. Ensure the system is backed up before you upgrade it and all you risk is your time.
If you're talking about something that needs five nines availability perhaps it's you need to do some dry runs first but in this case we don't appear to be.
Admin
Hardly a story to qualify as a wtf. The whole emphasis is on the SQL statement and how it could've been replaced with a shorted SQL query.
It is pretty easy to imagine that the second contractor realised the problem of the datestring sorting and instead implemented a hack to make it work within those limitations.
As the true solution to this problem is already reported not feasible on that version, it looks like the second contractor was actually an intelligent guy to come up with this query.
I've seen many such implementations (or ugly hacks) where the system was designed poorly from the ground up. It does not mean that programmers who inherited those systems are not good.
Admin
I dunno, what I would do is create a secondary date column. After every run of the Oracle export, use a cron job or trigger to convert the dates into yyyy-mm-dd, then save them. Then you're just stuck with the job of updating all the the queries to reference the new date column.
Admin
Hmm... assuming upgrading wasn't possible, how about this hack:
Ugly? Hell yeah, but it gets the job done in 10 minutes, is more legible than the contractor's hack, shouldn't impact efficiency and there's no need to refactor any code at all. Amirite?
Admin
You win the thread.
Admin
php has a strtotime() function as well, no reason he couldnt fix the column in question, and if he's worried about breaking it, he could use another column side by side.
ohh and, the real wtf isnt he was using php or mysql, its that the company who decided on php/mysql didnt hire anyone competent enough to create a decent site.
Admin
Obviously they chose NUMERIC(12) because (10) and (11) gave them weird error messages. ;-)
Admin
Could you, please, provide the number you would store for say, 2009-July-15? Note:
I don't care about the time.
I do want to be able to compare dates from various time zones (in case that matters ... you can pick your own time zone)
Note that there are such things as:
a) Daylight Saving Time (which we can hope will happen as per any rules but we're never sure whether government will decide to change those ... e.g. abolish them)
b) Leap seconds
When you're done with that, please tell me which date is represented by 1,247,615,995,679?
Admin
The ANSI requirements for time zones in several of the date/time types can make life difficult for database implementors.
PostgreSQL uses doubles in seconds with January 1, 2000 as the epoch for TIMESTAMP and DATE types (by default). Calculations are done with Julian dates but the internal representation is an 8-byte double (NULLity for column values is stored separately in the row header). There is another deprecated date/time type which is a 4-byte Unix epoch-based timestamp, but most of the date/time functions and operators don't work with it.
PostgreSQL doesn't store the time zone--it converts the timestamp given by the client at the time zone given by the client (or taken from session or system configuration) into UTC and stores that, and does the opposite conversion on retrieval (the end result is that all timestamps in the database are presented in the local time zone, but stored in UTC). That design decision is somewhat controversial.
Oracle stores the timestamp value in the data itself (consuming extra space to do so). Arguably Oracle is better when the DST rules change, since you can always retrieve whatever the client thought was the local time of the timestamp, but PostgreSQL packs more precision in less space, which is important if your database is I/O bound and 37.5% smaller means 37.5% faster. Besides, only an amateur database developer (cough) would choose the wrong date/time/timestamp types for their application (cough Outlook cough) and get confused by changes to DST rules.
I'm not sure how query optimization enters into the picture. Date range queries can use simple btree indexes of any date representation whose lexicographical ordering is identical to the represented value's chronographical ordering. Queries that try to extract individual fields in strange ways (e.g. month or minute number modulo 7) must use indexes purpose-built for such queries, or scan the entire table. The representation of the column value is irrelevant for query optimization--only the representation of index key values matters, and that can be different from the column representation if that's what the index needs.
The only savings I can see of the Oracle representation is slightly lower CPU overhead, but that overhead is negligible compared to the overheads of other parts of the data retrieval process, or for that matter the overhead of using more storage space for less precision.
Admin
If you think THATS bad, a collegue of mine decided that timestamps were a hassle and you would have issues when entering birthdates before 1970 (I asked him why negative values would be a problem but couldn't answer me this 4 years later). Instead, we now have a dayfield which (as you might guess) expresses the date in the number of DAYS after 1-1-1970.
To make matters worse, this field actually used negative numbers to go back into time (before 1970). Worst part of the whole thing is the fact that hes going to work somewhere else leaving me to slowly phase out the fancy dayfield in favor of the vanilla timestamp (or datefield - depending on the usage).
With pain in my heart I scheduled the deletion of the dayfield library with all its conversion functions... The good old times...
Admin
"ullamcorper"? WTF?
Admin
I that the same as int(11) in MySQL?
Admin
How many courses can the university have? This is a "small educational institution". Memory is cheap and CPU's are pretty fast. Just read the results in an array and sort it using PHP. I bet it won't use any more resources than that ugly SQL hack.
And if the small institution is slashdotted, just cache the course listings. Then you would just have to generate it once every semester...
Admin
I'm not surprised by this. Oracle and MySQL have totally different default representations for a date. They also have totally different semantics for converting between representations of a date. Its far easier to get them to interoperate if you use a string, although I prefer yyyy-mm-dd since it sorts properly.
Admin
I'm working for a company that still seems to be using Foxpro 2.6 for DOS and RPG II. We are migrating applications to C# and the .NET Framework.
Admin
Hey guys! I found the Real WTF (tm).
At the very least, consider unit tests on your new services.
Admin
Why couldn't he add a DATE column and use PHP to parse the date data into the new column and fix the Oracle logic and then drop the old column, renaming it to the old column name?
Admin
Both Oracle and PostgreSQL use Astronomical Julian Days. This is probably the easiest encoding to count dates accurately.
Since no society uses Julian Days, we can easily convert back the Gregorian or Persian solar calendars, or the Chinese ot Islamic lunar ones. Oracle has some support for all that.
It makes no more sense to store dates as DD-MM-YYYY than to store decimal numbers. Computers use binary, and ought to use Julian Days.
Admin
Until some asshole realizes that a whole lot of legacy code can be kept working if the epoch is reset to some carefully chosen Thursday in 2106...OK, maybe not.
Actually it's much more likely that a bunch of mixed 32/64 systems will pass Unix timestamps back and forth, silently truncating the timestamps at the lower 32 bits. The good news is that for 68 years or so it will be reasonably obvious when this happens.
Admin
I think I am one of the few people who actually gets that joke.
Admin
Admin
Admin
Well, it could be even worse (tm).
I once saw a similar system where dates where stored (in SQL Server) as char strings and we were asked to locate all entries for a given month.
Unfortunately, the very WTF'ed code inherited the "date to string" format from the individual client machine setting, so, say, "02-04-05" could be:
Feb 4th 2005 Apr 2nd 2005 Apr 5th 2002
or other funny combinations (we also saw combinations of four- and two-digit years)
So, give me all entries for April?
We gave up - there was no way to tell which of the formally valid interpretations of a date was the correct one. To the best of my knowledge, the company still cannot locate entries for any given month.
Bo, the ancient mainframer
Admin
I've had to work around a similar issue, resulting from importing a 200GB database from an AS/400 system. The import itself was a nightmare -- we were given the schema of tables we had to import, but were not told that the character set was EBCDIC (not ASCII!) and that numerics were packed BCD, with a sign nybble. Anyway, once we got the data into SQL server, the next step was to use DTS to transform the source tables into something properly meaningful. However, we had 5 years worth of data, of which only a subset date-range needed to be extracted. The snafu was that dates were all stored in character strings, over two fields, of the format 'yyyymmdd' (and for good measure the second field held 'hhss', with leading zeros removed! I.e. midnight was represented by '0'). We introduced a new sproc that took the date and time numeric fields, and returned a new DateTime. This is far less unwieldy than pasting the DateTime conversion code into each ORDER BY or WHERE statement. Ultimately, because we were working with around 100M rows, we decided to simply add a new column to the table containing the result of executing that sproc.
Admin
And that is the root of almost all of our problems.