How to get the millisecond value from a Timestamp field in firebird with Delphi 2007

Question

I have a Firebird database (running on server version 2.1.3) and am connecting to it with Delphi 2007 using the DBExpress objects (using the Interbase driver)

One of my tables in the database looks something like this

CREATE TABLE MYTABLE
(
  MYDATE Timestamp NOT NULL,
  MYINDEX Integer NOT NULL,
  ...
  Snip
  ...
  PRIMARY KEY (MYDATE ,MYINDEX)
);

I can add to the table OK, and in Flame Robin it shows the timestamp field as having a millisecond value.

But when I do a select all (select * from MYTABLE) on the table I can not get the millisecond value, as it is always returned as 000.

This causes major problems as it is part of the primary key (unfortunately I didn't design the table and don't have authority to change it).

I have tried the following to get the millisecond value:

sql1.fieldbyname('MYDATE').AsDateTime;
sql1.fieldbyname('MYDATE').AsSQLTimeStamp;
sql1.fieldbyname('MYDATE').AsStirng;
sql1.fieldbyname('MYDATE').AsFloat;

But they all return 14/09/2009 14:25:06.000 when formatted.

How do I retrieve the millisecond from a timestamp?

UPDATE: In case this helps anyone in the future, here are the drivers I tried for DBExpress and the results.

• Embarcadero - dbExpress Driver for Firebird (Delphi 2010 Trial Version) - Milliseconds not supported in timestamps.
• Chau Chee Yang's - dbExpress Driver for Firebird (Delphi 2007) - Milliseconds not supported in timestamps.
• UpScene - InterXpress for Firebird (Delphi 2007) - Milliseconds are supported in timestamps.
• DevArt - dbExpress Driver for InterBase (Delphi 2007) - Milliseconds are supported in timestamps.

Answer 1

A colleague of mine (Edwin van der Kraan) just checked: it works with FIBplus!

He checked with FIBPlus version 6.9.6 and Firebird 2.1.1.

He inserte a current_timestamp insert, and extracted it with FormatDateTime('dd-mm-yyyy hh:nn:ss:zzz', TestpFIBDataSetMYDATE.Value) Then he got back the date and time including milliseconds.

--jeroen

Answer 2

I don't know if Firebird actually supports milliseconds, but I know for a fact that the InterBase driver for Delphi 2007 does not. This is because InterBase does not return milliseconds in its C API; although InterBase stores DateTimes to sub-milliseconds precision, the InterBase API returns times in a C TM struct which does not contain a millisecond field. Therefore, the dbExpress driver designed for InterBase is not going to support that. So, you will need to change drivers at a minimum, presuming that Firebird actually supports this at all.

Answer 3

Firebird FAQ #305 explains how to easily work around this via simple varchar CAST.

Answer 4

Firebird dbx drivers exist (you can try them):

http://sites.google.com/site/dbxfirebird/ (free)

http://www.upscene.com/products.dbx.dbx_fb.php