Login/Register
How to get the millisecond value from a Timestamp field in firebird with Delphi 2007
Asked Answered
Solved sqldelphifirebirddelphi-2007dbexpress
O

4

5

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.

Oubre answered 14/9, 2009 at 15:28 Comment(0)
C
4

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.

Continuative answered 14/9, 2009 at 15:37 Comment(1)
IBExpert can retrieve milliseconds from a InterBase 7.5.1 database - but Delphi 2009 does not read the millisecond part. Is there a newer version of Delphi available which supports the ms part? - see #5272299 – Krum
M
5

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

Maurine answered 14/9, 2009 at 18:55 Comment(0)
C
4

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.

Continuative answered 14/9, 2009 at 15:37 Comment(1)
IBExpert can retrieve milliseconds from a InterBase 7.5.1 database - but Delphi 2009 does not read the millisecond part. Is there a newer version of Delphi available which supports the ms part? - see #5272299 – Krum
R
3

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

Riggall answered 3/5, 2010 at 10:32 Comment(0)
H
1

Firebird dbx drivers exist (you can try them):

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

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

Hex answered 14/9, 2009 at 19:19 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.