Legacy system, indeed.



  • I'm working on an ERP system migration.  The current legacy system is considerably older than I am, and based on technologies created at a time that I have never been able to pin down.  Some things look like they were added in the '90s, others stem from the '70s.

    While working on data migration, I noticed that a date field was giving me some really weird results when I dumped it from what I think was some early version of what is today called a "database".  When using the console interface, the dates displayed as typical-looking dates - e.g. 2001/12/15.

    But, when exporting from the database back-end, I'd get values like these:  2458228, 2454576, 2455409, 2451584, and so on.

    I was pretty sure those weren't dates concatenated into strings, or any form of UNIX time I've ever seen.  The software vendor wasn't much help either; they'd never seen it before.  Being as I have an interest in history, I opened up some of my vintage computing books, and worked my way backwards chronologically.

    By the time I got back to the 1940's and was looking at telephone-relay based computers, I was pretty much ready to give up hope.  On a lark I went to the ancient history section of my library and opened a book that was about old timekeeping systems.

    It didn't take me long to figure it out then.

    This legacy system stored the dates as integers representing dates in the Julian Calendar (created in 45 BCE, and largely retired some centuries ago).

    I can't figure out why it would store this one field as an integer-Julian date, while every other date in the system is stored as a string.  Then again, this system also stores "multi-value" field data delimited with pipes, and predates relational database design - so just about any hope at finding sanity in this system is best abandoned.



  • Are you sure it's the Julian calendar (created in 45BC, differing from the modern Gregorian calendar only by the treatment of leap years, and used almost exclusively by the Eastern Orthodox Church), and not the Julian Date (created in 1849, widely used by astronomers, and occasionally used by historians)?



  •  @Carnildo said:

    Are you sure it's the Julian calendar (created in 45BC, differing from the modern Gregorian calendar only by the treatment of leap years, and used almost exclusively by the Eastern Orthodox Church), and not the Julian Date (created in 1849, widely used by astronomers, and occasionally used by historians)?
    That makes way more sense than my theory and mixed up terminology. That's what you get when you mix wikipedia with insomnia and tears.

    Thanks for clearing that up.

    EDIT:   That said - can anyone explain why such a system would use a Julian date rather than a string?  Was this a common thing at one time?



  • @KrakenLover said:

    EDIT:   That said - can anyone explain why such a system would use a Julian date rather than a string?  Was this a common thing at one time?


    Julian dates as such have not been commonly used by computer systems, but OpenVMS uses the Modified Julian Date (epoch of November 17, 1858), and COBOL uses the ANSI date (epoch of January 1, 1601). If the system had to interface with one or the other, or if that programmer had a COBOL background, that could be where the use of Julian dates came from.



  • @Carnildo said:

    Julian dates as such have not been commonly used by computer systems, but OpenVMS uses the Modified Julian Date (epoch of November 17, 1858), and COBOL uses the ANSI date (epoch of January 1, 1601). If the system had to interface with one or the other, or if that programmer had a COBOL background, that could be where the use of Julian dates came from.
    That's pretty interesting.

    This system was developed by one company and the rights and source sold to another at least once; if not more.  So I really don't know its exact origins.

    The technology it uses is something like IBM DB2, or some version of it.  But it has parts that seem older, like from the IBM Pick era.

     



  • It could be a Pick date. Pick stores date as an integer representing the number of days since 12/31/1967. If that is what you are seeing, I would urge caution when doing the conversion. I found in the past that Microsoft and D3 Pick disagree on the number of days between 12/31/1967 and today. At one point I figured out who was right, and where the difference came from, but that was YEARS ago.



  • Julian dates aren't really used in computer systems I know but, but they are well-documented and widely-used elsewhere, so seeing them in a database really isn't much of a WTF, IMO. The only WTF here is the complete lack of documentation.



  • The Julian date is expected to be an unambiguous way of assigning a number to a day in a way that can be used across many database back-ends which may require primary keys to only contain integers.  I have had the pleasure of working with a large ERP which only used Julian dates in the main financial transactions table.  It also had reverse dates, which were used because it was once built to run on a back end which couldn't sort in descending order. That would be tolerable if the reverse date only appeared as an additional helper field but sometimes it was the primary key and there was no normal date field.

    The one that made me really shout WTF across the office was the "O0" table file.  The font used for all of the documentation didn't distinguish between "O" and zero, so it took me a long time to find that little easter egg.



  • @blakeyrat said:

    The only WTF here is the complete lack of documentation.
    It's not just a lack of documentation.  The vendor who created/maintains this system had no idea about this Julian date, and they claim to have never seen it before in any of their past migrations.  This vendor doesn't even know their own system, and asks me technical questions about it - as if I know any more than they do.

    And the system we are moving to is a new one also designed by that same company.  I asked for a manual or user guide - there isn't any.  Nothing.  Zilch.

    All the training, operation, and maintainance for the system is based purely on trial & error and tacit knowledge.  The customer is 100% responsible for documenting the software themselves.

    Seriously.

    @garretro said:

    It could be a Pick date. Pick stores date as an integer representing the number of days since 12/31/1967. If that is what you are seeing, I would urge caution when doing the conversion. I found in the past that Microsoft and D3 Pick disagree on the number of days between 12/31/1967 and today. At one point I figured out who was right, and where the difference came from, but that was YEARS ago.
    That's really interesting too - I'm going to have to run some tests and see if I can figure out how to convert that date properly.

    @Qwerty said:

    The Julian date is expected to be an unambiguous way of assigning a number to a day in a way that can be used across many database back-ends which may require primary keys to only contain integers.  I have had the pleasure of working with a large ERP which only used Julian dates in the main financial transactions table.  It also had reverse dates, which were used because it was once built to run on a back end which couldn't sort in descending order. That would be tolerable if the reverse date only appeared as an additional helper field but sometimes it was the primary key and there was no normal date field.

    The one that made me really shout WTF across the office was the "O0" table file.  The font used for all of the documentation didn't distinguish between "O" and zero, so it took me a long time to find that little easter egg.

    Sounds like you were worse off than me.  Though, at least you had documentation.  But I have the good fortune of not needing to work with the order history or financial side of the system... thus far, anyway.


  • @KrakenLover said:

    I can't figure out why it would store this one field as an integer-Julian date, while every other date in the system is stored as a string.

    Are you sure the system actually stores any dates as strings (as opposed to just being converted to/from them in the user interface)?

    All software I know that actually has support for dates (i.e. not counting sqlite) stores them as number of someunit since someepoch. Usually the someunit is seconds or milliseconds if time is included and days if not, but some systems, like Excel, use fractional number of days. The epochs are rather random numbers. The standard Julian Date epoch is rather reasonable choice, because it is at least long standing standard.



  • @KrakenLover said:

    EDIT:   That said - can anyone explain why such a system would use a Julian date rather than a string?  Was this a common thing at one time?

    Why would you store a date as a string? That's just stupid. Store dates as numbers, unix timestamp for example. That way you can do math with it, you know, the thing computers are designed to do. Want all records between date X and Y? Easy stuff with timestamps, good luck with strings. Anyone storing dates or times as anything else but basic numbers (and not some stupid YYYYMMDDHHSS format in an 64 bit int) should be shot in the feet.



  • @garretro said:

    It could be a Pick date. Pick stores date as an integer representing the number of days since 12/31/1967. If that is what you are seeing, I would urge caution when doing the conversion. I found in the past that Microsoft and D3 Pick disagree on the number of days between 12/31/1967 and today. At one point I figured out who was right, and where the difference came from, but that was YEARS ago.
     

    I think you mean the Lotus 1-2-3 compatibility in Excel and other products?



  • @Daid said:

    @KrakenLover said:

    EDIT:   That said - can anyone explain why such a system would use a Julian date rather than a string?  Was this a common thing at one time?

    Why would you store a date as a string? That's just stupid. Store dates as numbers, unix timestamp for example. That way you can do math with it, you know, the thing computers are designed to do. Want all records between date X and Y? Easy stuff with timestamps, good luck with strings. Anyone storing dates or times as anything else but basic numbers (and not some stupid YYYYMMDDHHSS format in an 64 bit int) should be shot in the feet.

    If you have to ask, you are obviously a troll who doesn't understand how comparing strings works. :)



  •  I work with a system that translates dates to/from Julian to simplify date math (basically its equivalents of DATEADD / DATEDIFF), but it's documented and run through a central library, and not actually stored that way.  It's also occasionally used the reverse-date method, though I think that was eliminated at some point.

     



  • @flop said:

    @garretro said:

    It could be a Pick date. Pick stores date as an integer representing the number of days since 12/31/1967. If that is what you are seeing, I would urge caution when doing the conversion. I found in the past that Microsoft and D3 Pick disagree on the number of days between 12/31/1967 and today. At one point I figured out who was right, and where the difference came from, but that was YEARS ago.
     

    I think you mean the Lotus 1-2-3 compatibility in Excel and other products?

    No, I meant Microsoft as a whole. Everything I tested at the time showed the same discrepancy. I checked several to make sure I wasn’t going crazy. I found the same difference in Excel, Access, VBA, VB6 and .Net (1.1).



  • @Daid said:

    @KrakenLover said:

    EDIT:   That said - can anyone explain why such a system would use a Julian date rather than a string?  Was this a common thing at one time?

    Why would you store a $DATATYPE as a $OTHERDATATYPE? That's just stupid. Store $DATATYPEs as $DATATYPEs

    CS101TFY.




  • @Bulb said:

    Are you sure the system actually stores any dates as strings (as opposed to just being converted to/from them in the user interface)?

    All software I know that actually has support for dates (i.e. not counting sqlite) stores them as number of someunit since someepoch. Usually the someunit is seconds or milliseconds if time is included and days if not, but some systems, like Excel, use fractional number of days. The epochs are rather random numbers. The standard Julian Date epoch is rather reasonable choice, because it is at least long standing standard.

    Every other date field gets dumped out as a string - looking at the fields' raw data in the admin tool shows them as strings.  This is the one field that is stored as a Julian date, and then converted into a string for display in the GUI.

    @Daid said:

    Why would you store a date as a string? That's just stupid. Store dates as numbers, unix timestamp for example. That way you can do math with it, you know, the thing computers are designed to do. Want all records between date X and Y? Easy stuff with timestamps, good luck with strings. Anyone storing dates or times as anything else but basic numbers (and not some stupid YYYYMMDDHHSS format in an 64 bit int) should be shot in the feet.
    I understand that concept, thank you.  But my question was not about storing dates as strings in general, but was rather in the scope of this one system where every single other date is stored as a string and this one particular field did not follow the convention used everywhere else in the system.

    As I said before, this system in ancient and full of WTF.  Despite the issues with storing dates as strings, or whatever else, even though doing such things cause problems, it still would have been far, far, easier to deal with than this undocumented inconsistency.

    I should have made my question more specific, I guess.

     



  • @garretro said:

    It could be a Pick date. Pick stores date as an integer representing the number of days since 12/31/1967. If that is what you are seeing, I would urge caution when doing the conversion. I found in the past that Microsoft and D3 Pick disagree on the number of days between 12/31/1967 and today. At one point I figured out who was right, and where the difference came from, but that was YEARS ago.
     

    Another theory: it's a PARS date (see also ACP, TPF and SABRE).  Did the OP's environment evolve from anything involving airline reservations?



  • @da Doctah said:

    Another theory: it's a PARS date (see also ACP, TPF and SABRE).  Did the OP's environment evolve from anything involving airline reservations?

    I dont think so - this ERP is oriented for publishers.

    But, like I said, this system changed hands a number of times, and just about anythign is possible.

     



  •  The military uses Julian dates almost exclusively in most of their legacy systems.  And since they don't want to train their users on a new interface, even the newer systems that replace the legacy systems use Julian dates.

     

    The one here doesn't actually store them in Julian format in the database though, at least not when we want to do any sort of date-specific business logic on it.



  • @Bulb said:

    All software I know that actually has support for dates (i.e. not counting sqlite)...


    By coincidence I am just trying to get sqlite run on an obscure system. While it stores dates as strings (in ISO format so they sort sanely), for internal calculations it actually uses... Julian Day Number:
    @http://sqlite.org/c3ref/vfs.html said:
    The xCurrentTime() method returns a Julian Day Number for the current date and time as a floating point value. The xCurrentTimeInt64() method returns, as an integer, the Julian Day Number multiplied by 86400000 (the number of milliseconds in a 24-hour day).



  • @garretro said:

    I meant Microsoft as a whole. Everything I tested at the time showed the same discrepancy. I checked several to make sure I wasn’t going crazy. I found the same difference in Excel, Access, VBA, VB6 and .Net (1.1).

    And let's not forget the original (does it still exist?) discrepancy between date 'numbers' in Excel for PC compared to Excel for Mac. My memory is hazy, but they were some unhelpful number different (about four years apart from one another, seems to be what I hazily recall).



  • @Cad Delworth said:

    @garretro said:
    I meant Microsoft as a whole. Everything I tested at the time showed the same discrepancy. I checked several to make sure I wasn’t going crazy. I found the same difference in Excel, Access, VBA, VB6 and .Net (1.1).

    And let's not forget the original (does it still exist?) discrepancy between date 'numbers' in Excel for PC compared to Excel for Mac. My memory is hazy, but they were some unhelpful number different (about four years apart from one another, seems to be what I hazily recall).

    The Excel epoch in Windows was 1900, the epoch in Mac Classic was 1904 (same as the OSes, which made things easy.) (Well, kinda. Mac Classic didn't really have an epoch, but 1904 is where everything started counting from...)

    This is because at the time both products were new, transferring files from a Mac to Windows machine wasn't just a low-priority feature, but virtually impossible to do unless you were a technical super-genius. So it didn't matter that the two products had different epochs.

    Of course, despite this, you might as well go on a horrible rant about how awful Microsoft's products were, because their developers didn't have ESP and couldn't foresee problems that would occur in a mere 5 years from the product's release! Stupid Microsoft! I mean Micro$oft!! Don't bother educating yourself, just rant rant rant!


  • Discourse touched me in a no-no place

    @Cad Delworth said:

    about four years apart from one another
    4 years and a a leap day (Jan 1st 1900 vs 1904.)



  • Our CRM system stored rule dates in its incentive compensation module as Julian dates in the database. (We've been doing incentive compensation in an external system for the last five years or so, so it's been a while since I've had to deal with that.) Other than that, I haven't seen them used anywhere.

    As far as Excel goes, the additional problem with the 1900 date system is that it includes the nonexistent day 29/02/1900.


  • Considered Harmful

    @Scarlet Manuka said:

    As far as Excel goes, the additional problem with the 1900 date system is that it includes the nonexistent day 29/02/1900.

    What, there's no second of Icosikaiennovember, 1900?



  • My POS legacy-system was made in Clarion 6.0 and chose to store dates as Clariondates. Epoch of 1/1/1800.

    They also chose to not let the GUI handle conversions, but sprinkled the DB with functions to convert. There are at least 4 functions with names like dbo.fn_ClariondateToDatetime and dbo.fn_ClariondateToISOString and the like. At least they gave them descriptive names...

     Edit for brainfart.



  • Doing a little more digging, I found a reference in one of the DB driver to PICK - so it looks like this sytem is related to PICK in some way.

    Also - After going through the data dump some more, I discovered that some of the date entries in the same field are stored as the Julian Date-looking integer, and some of them are stored as strings using typical US-style dates (e.g. mm/dd/yyyy).

    This... what.... I....

     



  • @KrakenLover said:

    some of the date entries in the same field are stored as the Julian Date-looking integer, and some of them are stored as strings using typical US-style dates (e.g. mm/dd/yyyy).
     

    Abortive attempt at Y2K amelioration?



  • @da Doctah said:

    @KrakenLover said:

    some of the date entries in the same field are stored as the Julian Date-looking integer, and some of them are stored as strings using typical US-style dates (e.g. mm/dd/yyyy).
     

    Abortive attempt at Y2K amelioration?

    I wish this whole system had been aborted.  But nooooooooo, even the most unloved, hideous, repulsive, vile things have a right to live.

    What we need are more developers who are pro-choice, and willing to abort once they realize their baby is actually demon-spawn who will grow up to become monolithic systems that only serve to torture those forced to work with them and, in their death-throws, will inflict pain and suffering beyond all imagining on the poor souls doomed to spend the final shreds of their sanity putting the system down.

     



  • @KrakenLover said:

    What we need are more developers who are pro-choice, and willing to abort once they realize their baby is actually demon-spawn who will grow up to become monolithic systems that only serve to torture those forced to work with them and, in their death-throws, will inflict pain and suffering beyond all imagining on the poor souls doomed to spend the final shreds of their sanity putting the system down.

    Are you saying that there are systems that aren't like this?


Log in to reply