Date versus timestamp

[Thomas Mercer-Hursh]

I am doing some work loading directly into t_package and I notice that the SQL for creating an OpenEdge repository uses DATE, not TIMESTAMP for the date fields in it while in the EAP file these are clearly date-time fields.

Easy enough to fix ... or is this just some holdover on support for versions before the datetime datatype was introduced.

[«Midnight»]

TIMESTAMP fields are usually a special type for recording a (possibly unique) value in a record. While they can sometimes (depending on the DBMS engine) be used to store date and time information, this might produce unexpected results later.

In the case of EAP files, you are seeing the name that Jet (the engine behind MS Access) gives to date fields. Many Access data types were given names that were supposed to be user friendly, back in version 1.0 (such as Yes/No fields for boolean data). There are several synonyms that may also be used, based on similarly shaped fields in other engines. These are largely a convenience for data transfer between Access and other DBMS engines.

David

[Thomas Mercer-Hursh]

Perhaps I wasn't clear ... in the EAP file, these columns contain datetime information.  In an EA repository built on OpenEdge, they contain only date information.  This is because the script for building the schema uses DATE.  But, OpenEdge supports both datetime and datetime-tz datatypes, so there is no reason for it to be limited to date only.

I am speculating that this is just because the script was created for databases prior to the support of datetime.

[«Midnight»]

I am speculating that this is just because the script was created for databases prior to the support of datetime.

Do you mean that OpenEdge has only recently had a DATETIME type? If so, then what you suggest sounds quite possible.

Of course, it might as easily be that EA only uses (or used at some prior time) the date portion of the field. In the Jet engine there is no difference between the types, but perhaps the script writers for OpenEdge used DATE if it more closely approximated how the field would be used.

[Thomas Mercer-Hursh]

Yes, one of the embarrassing little stories about OpenEdge was that SQL access got a datetime some time ago, but it took ages for this to get added to ABL access.  Silly, really.

As for the usage ... that is why I ask the question.  I could readily change the script, but I don't know whether it is the right thing to do.

[«Midnight»]

I really cannot advise you on that. If you do change the script you will need to maintain it. Though Sparx makes few changes to the EA schema, IMHO we're overdue for one.

You can always do this in a roundabout way. EA is quite tolerant of 'extensions' to the schema. These could include such things as additional fields in a table, additional tables, or stored procedures. As long as you don't do anything too strange you can probably use one or more of these approaches to get the best of both worlds.

[Thomas Mercer-Hursh]

If I had a specific need for it, I would consider publishing my own version, especially since I am already putting together a variety of pieces of information for OpenEdge users of EA (http://www.oehive.org/EA).  But, my real question at this point is more one of what Sparx thinks.  If Sparx thinks that DATE is the right thing because that is the way it is used, then I should leave it alone.  If Sparx thinks that they script should be that way because they only want to publish one script and don't want to pose problems from users of older versions, then that is fine too, but I could consider publishing both versions on my site.  Or, I could make the changes and give it back to them.  The problem at this point is only that I don't know.

Chances are, of course, that it doesn't really matter.  Probably, these columns are just annotations and if they are datetime one gets more detail than if they are date only and that is the end of it.

[«Midnight»]

Actually, it might be worth inspecting the SQL. If the syntax for storing a date in the two column types is the same then you could probably go ahead without worry. I think this is a good possibility - it might very well be in some of the supported DBMS dialects; you should check several - so why not see if it works for you.

Of course this seems to be almost the reverse case with SQL Server. There is only one corresponding type now, which essentially corresponds to DATETIME. However, in SQL Server 2008 there is a new DATE type, which appears to support the sort of thing EA appears to be doing.

David

[Thomas Mercer-Hursh]

How would I look at the SQL?

The reason I am guessing that EA is treating it like a datetime is that there are non-zero times in the actual fields.  Generally, if one uses a datetime datatype to store simple dates, the time ends up as 00:00:00.

[«Midnight»]

Sorry about that Thomas. I was not very clear about that.

What I meant was to take a look at the SQL syntax that OpenEdge - or any other DBMS you might be using - employs to read or write to the two field types. Are they the same? And if a time is included in the write statement (insert or update) will the DATE syntax throw an error?

As to time information being stored in some cases, what you observe would be normal if someone did the equivalent of storing the value NOW to a DATETIME field. Such a behavior is common in MS Access and ORACLE among others. If the application later reads the value back it will see a non-zero time component. However, the date component will be correct. The application is responsible for either (or both of) clearing the time when the field is populated, or trimming the time when the field is read.

David