Milliseconds shown in report although format is hh:mm:ss

Hi, I have a report with a table that shows this value from a postgres server: ..., (max(enddate) + ( intervals * '1 minute'::interval )) - now() as until_schedule... When displaying this value in the table it is shown as 00:01:48.950568 for example. although I have set the date/time format for that data field to hh:mm:ss and would expect 00:01:48 Is there a specific reason for this behaviour and how can I resolve it?Thanks for any hint! Max

Find more posts tagged with

Comments

micajblock

What is the data type? It is probably returning a string.

Max Bo

Just checked it, you are right: it is a string ...

Is there a way to specify in the SQL what the data format should be?

micajblock

The database decides on the format. Why don't you just to a sub string and get the first 8 characters?

Clement Wong

The SQL function in your first post returns an "interval" PostgreSQL date/time data type (<a data-ipb='nomediaparse' href='http://www.postgresql.org/docs/8.2/static/datatype-datetime.html#AEN4903'>http://www.postgresql.org/docs/8.2/static/datatype-datetime.html#AEN4903</a>). For example, the result could be "-18 days -17:40:36.141333". BIRT would interpret this as a string. If you know that your result doesn't have any days, months, year parts in the interval (only time parts), you could probably update your SQL and have the result returned as time, and then BIRT can format date/time with the hh:mm:ss format: [color=rgb(0,0,255);] ([/color](max(enddate) + ( intervals * '1 minute'::interval )) - now()[color=rgb(0,0,255);])::time[/color] as until_schedule Otherwise, you can use three EXTRACT functions and build your own hh:mm:ss string in the SQL.