[Issue] Vexing problem with time stamps

[sefs]

I have a table with a timestamp field called TID. The format of this field is: YYYYMMDDHHMMSS, so I can get a nice string in the time stamp field

However I just added two records where I cannot select these records base on the TID field. Why? Because the fields for some reason are being rounded up.

for instance the TID has a visual value of: 20090912204955

But what is really stored in that field is: 2009-09-12 20:49:54 which i got from running this query "SELECT SUBSTRING(TID,1,19) FROM "mytable" WHERE (ID = 38);"

See that it's off by one second

so if i do a select on that field to filter by TID = "20090912204955" the record will never show up because the true value of the field is "20090912204954"

How can I solve this.

Thanks.

[Villeroy]

OOo's number formatter can not interprete a sequence of digits as anything else but integer.
http://www.openoffice.org/issues/show_bug.cgi?id=89532

[eremmel]

sefs,

Villeroy is pointing to a bug that might be the cause of your problem: that the entered value does not be translated to the value you are looking for. On the other hand if you insert a timestamp in your database it might be inserted with a higher accuracy than you expect or want to use. During representation of that value it might be rounded up to the desired format. You have here a problem that is similar to double values (to some extend). A common approach is to query with a certain accuracy. In your case something like:

Code: Select all

...WHERE ABS( DATEDIFF('second', "TID", '20090912204955')) <= 1 

But the when the literal value is coming from a form it might be scrambled by the bug.
An workaround might be to use a LIST box. In that way one prevent the conversion of the input value back to the date/time value and the accuracy/rounding issue. Give the LIST box a display value of the TID and use its corresponding ID to identify the record.

Success

[sefs]

Thanks for your solutions.

My schema looks like this.

Code: Select all

CREATE CACHED TABLE "bol_visitors_database"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"TID" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,"Date Registered" DATE,"Name" VARCHAR(255),"Address" VARCHAR(255),"Parish" VARCHAR(255),"Country" VARCHAR(255),"Telephone" VARCHAR(255),"Email" VARCHAR(255),"Birthdate" DATE)
ALTER TABLE "bol_visitors_database" ALTER COLUMN "ID" RESTART WITH 40

As you can see TID is a TIMESTAMP field. This is auto generated by following this tutorial here...

http://user.services.openoffice.org/en/ ... f=13&t=573

bascially I had to create the DB and then run this code

Code: Select all

ALTER TABLE <table name> ALTER COLUMN <colum name> SET DEFAULT CURRENT_TIMESTAMP

I am thinking it may be easier to change the TID field to a varchar field and some how cast the TIMESTAMP value to string, is any of that possible OR better yet is there a way to force a round up on the time stamp actually stored so that when I compare it it is the same vaule as what you see in the field?

[Villeroy]

What is easier to do with a string? A char field takes all kinds of chars. Nothing ensures that the field contains string representations of time values. No date/time function will work with strings.
No formatting should be that important that you sacrify the appropriate field types for it.

In the attachment I try to demonstrate a way to enter times as a sequence of digits by means of a pattern field.
It requires that you store an equivalent formatting pattern for the table. Otherwise the pattern field shows the digits in wrong order.
I assigned an ISO format YYYY-MM-DD HH:MM:SS to the table and used the same pattern of digits, hyphens and colons for the pattern field.
You enter only the YYYYMMDDHHMMSS digits. The hyphens and colons are preset.

Edit: It seems to work properly with stamps >= 1899-12-30 1899-12-30 11:11:11 is OK 1899-12-29 11:11:11 gives 1899-12-31 00:00:00

Add this constraint to avoid entering wrong values:

Code: Select all

DELETE FROM "STAMPS";
ALTER TABLE "STAMPS" ADD CONSTRAINT "Min_Date" CHECK (STAMP>='1900-01-01')

[eremmel]

I played with Villeroy's test database and noticed that at least with OOo 3.1.1 on XP that there is no fraction of a second stored in the database. I tried with inserting CURRENT_TIMESTAMP function and with literals like '2009-09-09 12:12:12.999'. Those values are stores as '2009-09-09 12:12:12.000'. To test this I changed the table date format to YYYY-MM-DD HH:MM:SS.000. So when you state that you are experiencing rounding issues, are you using an other database than the build-in HSQLDB database?
Has HSQLDB a finer granularity on linux? When this is the case you need to take case about it as proposed. Never the less the pattern method solves the issue when working with straight numbers. An other approach to find all records at certain date is to use eg.: WHERE YEAR(TID)||MONTH(TD)||DAY(TID) = '20090912'

Note: that I looses my user defined date format in the table definition of field STAMPS.STAMP when I save my database and reopen it again. I hope it works for you...

[sefs]

Since I did not need find granularity...i.e down to the milliseconds...

For example YYYY MM DD SS.SSSSSSSSS which was being stored... I just wanted everything before the decimals. In my case I can guarentee that no two records will be added in the same second So in the description of the table instead of using TIMESTAMP I used TIMESTAMP(0)...making use of the precision attribute. It now auto generates time stamps without the extra fluff after the decimal altogether and just stores everything before. This way works for my situation.

[jadawin]

Perfekt, i called the customer and this solution is better then the first wish the customer told me

Thank you