info@... wrote:

>I use Firebird primarily with Delphi and have had no problems with
>using and displaying dates (suitable for my country - UK.)

First thing to understand is that Firebird does not store dates
as strings. It can recognise string inputs for dates according to a
number of conventions but, at the back, it converts them to one or a
pair of numbers calculated from starting points on timescales.

What gets returned to the client when dates are read depends, as you
rightly guessed, on what the operating system is set up to display
and what the client interface offers for massaging date/time data
into strings. Most (if not all) Delphi interfaces use the Windows
regional settings.

>However, it seems to be a big problem with Firebird and dates when
>working with PhP with firebird driver. I have a number of fields that
>hold information in a field defined to be of type "date" in Firebird.
>When I use PhP to query my database I get the fields formatted in the
>ISO-8601 format (year/month/day.. etc).

Other than a UDF, there is nothing you can do in your SQL to influence
what string the client delivers to your interface. You should ask on the
firebird-php list for advice about this. PHP has so many little
functions to massage data that I'd be surprised if you didn't have
at least one convert option!

>I really dont know how to format the results so that they appear
>"normal" for my region - ie the dd/mm/yyyy format. I also am not sure
>where the problem originates. It seems to depend on the client
>software I am using to view the data (which seems to imply its the
>client software that is responsible for rendering the data.)

True. Although there is the (not highly recommended) option of
using an external function ("UDF") to convert your dates to strings.
There are various UDF libraries around: look at
https://www.ibphoenix.com/download/tools/udf
for links to a selection of such libraries. You might find one that
does what you want. Of course, strings are not going to be a lot of
use to you if your applications want to do calculations on dates.

>Here is a summary of softwa re I have tried and the results

>Program result
>FlameRobin Formatted correctly (except using the "." notation - so dd.mm.yy

Written in C++ using an interface called IBPP, which probably has a
function to massage dates into a format that is compatible with the
regional context of the host (or maybe the client).

>DBeaver Formatted incorrect for my region ( ISO-8601 format)

Depends on the driver you have behind this, I guess, and probably also
the regional settings on client or host or both. Firebird has lists
for both Java and ODBC/JDBC so it's worth asking on the appropriate
list.

>PhP Formatted incorrect for my region ( ISO-8601 format)

As above.

>Delphi (VCL) Formatted correctly dd/mm/yyyy

Follows the regional setting. This is easy to test.

>My question can be sumerised as - How to format the results so they
>are correct for a specific region (in this case the UK )? Should the
>client software (PhP/DBeaver etc) have a setting that allows you to
>format the results correctly ? Becasue I cant find any global setting
>in the various clients that will allow this.

>If we do have to explicitly cast / format the data - this adds a lot
>of work on the server / software and surely must have performance issues ?

Cast - no. But if you go the UDF route and export your dates as
strings then data over the wire will be fatter but work at the client
will be thinner. That's true of anything you convert, of course.
Can't avoid it.

>My goal is to format the result from a PhP request so the dates are
>displayed correclty. I understand that Firebird does not have
>the"convert" function - so I have tried using the "CAST&quo t;
>function - but this seems to do very little
>
>for example..
>cast(TRANSDATE AS DATE) as TRANS_CREATED
>
>This seems to do nothing. I have tried looking through the PhP.ini
>file for some way to influence how the data is formatted and cannot
>find anything. I have even tried using ..
>
>ini_set('date.timezone', 'Europe/London');
>
>But this also did nothing.

I hope you understand a bit more now about what's going on in these
interfaces. Good luck.

Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com