Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 657
Re0sless Avatar asked Sep 14 '09 15:09

Re0sless


4 Answers

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

like image 147
Jeroen Wiert Pluimers Avatar answered Nov 08 '22 04:11

Jeroen Wiert Pluimers


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.

like image 35
Craig Stuntz Avatar answered Nov 08 '22 05:11

Craig Stuntz


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

like image 3
Milan Babuškov Avatar answered Nov 08 '22 04:11

Milan Babuškov


Firebird dbx drivers exist (you can try them):

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

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

like image 1
Hugues Van Landeghem Avatar answered Nov 08 '22 04:11

Hugues Van Landeghem