how would you store this time "01:49:35.3569"

[tbtcust]

Hi all

I need to store this time 01:49:35.3569 in a db2 db version 8.

The data type, how it stored, and retrieved does not matter. However, I will need to present the time in the same format.

How should I handle this time format? What data type would you use?

Thanks in advance for any help.

 

[blom0344]

Non-standard date/time/timestamp formats could either be defined as char or varchar. However the special functions that work specifically on timestamps/time (like microsecond() / midnight_seconds) will then have no use.
What are you planning to do with such accurate time values.
Do you want to use them in calculations of some kind?

Ties Blom

 

[gregsimpson]

tbt103,

personally I would probably use timestamp, which goes much further than this. Would storing the date as well present your business/application with a problem? That way as Ties has pointed out you can still use all the in built functions of db2.

Alternatively (something I have yet to use), have you considered defining this column as a UDT?

Cheers
Greg

 

[tbtcust]

Hi. Sorry for the late reply.

I present the notion of storing the data along with the time today and it went over well.

The only real issues is how intense the conversion back to the time format I want to display is.

I'm sure there is some overhead with the substring, date to time conversion, and sorting on the substring time conversion.

Any thoughts about the added overhead?

 

[gregsimpson]

tbt103,

There's a number of ways you can convert back, you'd have to play around I guess with thigs like substr, or even a combination of the hours, minutes and microseconds functions.

It does look as though any ordering would probably have to be accomplished by db2 using tempspaces rather than being able to use an index etc if you went for a Timestamp definition.

There appears to be no absolute solution but a couple of variations for you to try, especially given the concerns you've raised.

Now we have fuller visibilty of your requirements, I'm wondering if there's any mileage in going with the data defintions Ties mentions above and if you do have a need to do time type arithmetic, look into casting for the various functions available. If you don't have any arithemtic, then I'd say Ties option is the one to go for.

Cheers
Greg

 

[tbtcust]

gregsimpson,

You have giving a lot to think about. Not sure which way we will go. There is a lot of data to go through so the lost of indexing will be a big deal. I gust we have to do some benchmarking and cost benefit and find some middle ground. Thanks so much.