SQLdb PostgreSQL interval
lazarus / 16-Databases

----------------------------------------------------------------------
goodname · 2012-06-20 22:47

Has anyone tried to use the PostgreSQL interval data type with SQLdb?
The stable release does not have the interval data type mapped so I'm
guessing that it has not been tried.

Want to store shelf life data and the interval type seams to be ideal.
There may be other approaches such as storing in a text and converting
to interval. Don't know if this would work. Would have to try it.

----------------------------------------------------------------------
Lacak2 · 2012-06-21 05:45

AFAIK interval support was added by
http://bugs.freepascal.org/view.php?id=19323
So I think, that it is already in FPC 2.6.1 and 2.7

May be that you are using FPC  <= 2.6.0

----------------------------------------------------------------------
ludob · 2012-06-21 06:54

The interval type was unfortunately implemented as ftTime which means
that intervals >=24:00:00 are cut off.  I guess shelf life data are
expressed in days.

BTW a similar problem occurs when doing a 'select now()-fdate from
table' which returns the result as an interval (fdate a date or
timestamp field)

----------------------------------------------------------------------
Lacak2 · 2012-06-21 12:33

Quote from: ludob on June 21, 2012, 08:54:11 amThe interval type was
unfortunately implemented as ftTime which means that intervals
>=24:00:00 are cut off.  Why do you think so?
When you use Field.AsDateTime, then IMO there will be also "day"
(integer) part not only "time" (fraction) part.
So you can use something like:
Code: [Select]  d := Field.AsDateTime;
  DecodeTime(d,hour,minute,second,millisecond);
  hour := hour + (trunc(d) * 24);

----------------------------------------------------------------------
goodname · 2012-06-21 12:51

I'm using the stable release which is currently Lazarus 0.9.30.4 and
fpc 2.6.0. Have not found a need to upgrade to a snapshot even though
there are a lot of good patches in there. Didn't know about the
interval patch.

Shelf life intervals are measured in days or even longer durations. It
is common for batteries to be measured in months. The patch
description says that it will fail on times>24:00:00
Lacak2 I see that you wrote the patch and think that it can handle
days. Suppose I now have a reason to get a snapshot and try it.

----------------------------------------------------------------------
ludob · 2012-06-21 15:33

Quote from: Lacak2 on June 21, 2012, 02:33:16 pmQuote from: ludob on
June 21, 2012, 08:54:11 amThe interval type was unfortunately
implemented as ftTime which means that intervals >=24:00:00 are cut
off.  Why do you think so?
I should have been more precise: Field.AsString will cut off days and,
as a result,  the dbcontrols cut off the days too.
QuoteCode: [Select]DecodeTime(d,hour,minute,second,millisecond);
hour := hour + (trunc(d) * 24);DecodeTime does not work with negative
values, it does an abs() before conversion. Intervals can be positive
and negative.
trunc(d) for negative values doesn't work in fpc 2.7.1 neither

Edit: The trunc(d) problem was my fault. I defined hour as word (as in
the DecodeTime parameters) and the negative result from trunc(d) just
caused an overrun resulting in big positive values.

----------------------------------------------------------------------
Lacak2 · 2012-06-22 05:40

Quote from: ludob on June 21, 2012, 05:33:51 pmQuote from: Lacak2 on
June 21, 2012, 02:33:16 pmQuote from: ludob on June 21, 2012, 08:54:11
amThe interval type was unfortunately implemented as ftTime which
means that intervals >=24:00:00 are cut off.  Why do you think so?
I should have been more precise: Field.AsString will cut off days and,
as a result,  the dbcontrols cut off the days too.
Yes it is true. It will require some hacking (f.e. use
TField.OnGetText) to display properly stored data.
Alternative would be create descendant of TTimeField, where would be
overriden method GetText and register this field in
DefaultFieldClasses