openinteract-dev

[Openinteract-dev] anyone knowledgable about cross-platform datatypes?

[Chris W. <ch...@cw...>]

This is a simple SQL question, but I figured there are enough folks 
on here who work with different database platforms to know: what's 
the most common datatype to hold a date and time value? I've been 
using 'datetime', but that seems to have gone away in the newest 
PostgreSQL release, being replaced by 'timestamp'

The type 'timestamp' is referenced as a SQL-92 datatype [1], but a 
MySQL page [2] uses 'timestamp' as a date plus time that is 
automatically set to the time of the last insert/update. 
FirebirdSQL/InterBase also seem to use 'timestamp' as a datetime 
column type.

So my question is: is there any consistent column type for this, or 
do I need to make this vary among database vendors as well?

Thanks,

Chris

[1] http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x2632%2ehtm
[2] 
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME
[3] http://www.firebirdsql.org/index.php?op=faq
-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.

Re: [Openinteract-dev] anyone knowledgable about cross-platform datatypes?

[Andrew H. <hu...@ll...>]

At 10:41 PM 1/5/2003 -0500, you wrote:
>This is a simple SQL question, but I figured there are enough folks on 
>here who work with different database platforms to know: what's the most 
>common datatype to hold a date and time value? I've been using 'datetime', 
>but that seems to have gone away in the newest PostgreSQL release, being 
>replaced by 'timestamp'
>
>The type 'timestamp' is referenced as a SQL-92 datatype [1], but a MySQL 
>page [2] uses 'timestamp' as a date plus time that is automatically set to 
>the time of the last insert/update. FirebirdSQL/InterBase also seem to use 
>'timestamp' as a datetime column type.
>
>So my question is: is there any consistent column type for this, or do I 
>need to make this vary among database vendors as well?

I haven't looked into it too much, but to add one more data point to the 
list, Oracle uses 'date' columns which store the date and the time.  I 
would expect you need to make this vary among the vendors, and have special 
cases for it.  What might be worse is the date/time formatting, I doubt 
that it's standardized.

-Andrew



>Thanks,
>
>Chris
>
>[1] http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x2632%2ehtm
>[2] 
>http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME
>[3] http://www.firebirdsql.org/index.php?op=faq
>--
>Chris Winters (ch...@cw...)
>Building enterprise-capable snack solutions since 1988.
>
>
>
>-------------------------------------------------------
>This sf.net email is sponsored by:ThinkGeek
>Welcome to geek heaven.
>http://thinkgeek.com/sf
>_______________________________________________
>openinteract-dev mailing list
>ope...@li...
>https://lists.sourceforge.net/lists/listinfo/openinteract-dev

Re: [Openinteract-dev] anyone knowledgable about cross-platform datatypes?

[Chris W. <ch...@cw...>]

Andrew Hurst wrote:
>> So my question is: is there any consistent column type for this, or do 
>> I need to make this vary among database vendors as well?
> 
> I haven't looked into it too much, but to add one more data point to the 
> list, Oracle uses 'date' columns which store the date and the time.  I 
> would expect you need to make this vary among the vendors, and have 

Yeah, I was afraid of this. Thanks for the Oracle note.

I'll probably make this change in the next release.

> special cases for it.  What might be worse is the date/time formatting, 
> I doubt that it's standardized.

Date/time formatting is *MUCH* worse, but fortunately that can be 
dealt with almost entirely within the application logic. Someone 
else's problem :-)

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.

Re: [Openinteract-dev] anyone knowledgable about cross-platform datatypes?

[Andrew H. <hu...@ll...>]

At 11:49 AM 1/6/2003 -0500, you wrote:
>Andrew Hurst wrote:
>...
>>special cases for it.  What might be worse is the date/time formatting, I 
>>doubt that it's standardized.
>
>Date/time formatting is *MUCH* worse, but fortunately that can be dealt 
>with almost entirely within the application logic. Someone else's problem :-)

Forgive me if I'm asking an obvious question, as I haven't actually gotten 
OI running on my machine here yet, but how will this work?  Will you set it 
up so that no matter what the database it returns the date in a standard 
format?  like DD-MM-YYYY HH:MI:SS, then require the app to format it as 
necessary...   Will there be a set of routines for formatting it 
built-in?  I've used Date::Calc and Date::Format before and they worked 
rather well, thought I'm not sure about speed/size (never had the need to 
check on them before).

I could see the db driver always returning it in the same format, then 
requiring it to be input in the same format as well.  Maybe a 
format_for_db() function, taking the format you currently have the date in 
and the date, and it returning the string formatted for what the driver 
expects...

Anyway, thats my rambling thoughts.  Is that what you meant above?

-Andrew


>Chris
>
>--
>Chris Winters (ch...@cw...)
>Building enterprise-capable snack solutions since 1988.

Re: [Openinteract-dev] anyone knowledgable about cross-platform datatypes?

[Chris W. <ch...@cw...>]

Andrew Hurst wrote:
> At 11:49 AM 1/6/2003 -0500, you wrote:
>> Date/time formatting is *MUCH* worse, but fortunately that can be 
>> dealt with almost entirely within the application logic. Someone 
>> else's problem :-)
> 
> Forgive me if I'm asking an obvious question, as I haven't actually 
> gotten OI running on my machine here yet, but how will this work?  Will 
> you set it up so that no matter what the database it returns the date in 
> a standard format?  like DD-MM-YYYY HH:MI:SS, then require the app to 
> format it as necessary...   Will there be a set of routines for 
> formatting it built-in?  I've used Date::Calc and Date::Format before 
> and they worked rather well, thought I'm not sure about speed/size 
> (never had the need to check on them before).

Currently OI doesn't really care what format the date is in. My 
problem with the different types comes about because we have to 
create the schema -- no choice there.

OI relies on you to know your database and what format the dates 
are returned in and what they need for an insert/update. This is 
partly a cop-out, but there aren't too many places where we have 
to rely on a date/time being in a particular format. When this 
does come up...

> I could see the db driver always returning it in the same format, then 
> requiring it to be input in the same format as well.  Maybe a 
> format_for_db() function, taking the format you currently have the date 
> in and the date, and it returning the string formatted for what the 
> driver expects..

...we can use SPOPS::Tool::DateConvert to coerce a database date 
into a standard object (likely Time::Piece). That buys us the 
same interface for all dates no matter where they're from.

Then on insert/update we need to put the date object into the 
necessary database format. The SPOPS tool allows you to specify 
the date type in a strftime format for parsing, so it's pretty 
simple to declare and transparent to use.

I'll stick a note about this on the wiki for whenever the 
packages get converted to the OI2.

Chris

-- 
Chris Winters (ch...@cw...)
Building enterprise-capable snack solutions since 1988.