Re: [firebird-support] Converting dates and the ISO-8601 format
ok thanks Lester, I will head over there and see whats happening. I like firebird a lot and have used it for years so Im kind of invested in it. In hindsight, perhaps - perhaps I should have gone with MySql (seems like everyone else has :)) You are right about it not getting much love, trying to find a web host that will run a firebird server is very hard and if you do - its usually a lot more than the same MySql service. Not sure why that should be, but hey ho - guess its what the market will bear. Will head over to the list you mentioned Cheers,, Dave
Re: [firebird-support] Converting dates and the ISO-8601 format
On 10/11/2018 11:19, i...@synapsesoftware.co.uk [firebird-support] wrote: > Fantastic! Works a treat! Wow, wonder how many other people are going to > be tripped up by this ! ? There is a list for firebird users on php firebird-...@yahoogroups.com It is true that Firebird does not perhaps get the love it deserves when using it with PHP and there are a few niggles that have crept in over the years as how PHP handles data has been changed. We *DO* need a few more people who can handle the complexities of C and changes to the interface to bring both the original ibase driver and the less flexible pdo_firebird driver up to PHP7 standards. The PHP developers keep threatening to drop them all together! So as a starting point, we could do with a bit more activity on the firebird-php list ... -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
Re: [firebird-support] Converting dates and the ISO-8601 format
Woops! Spoke to soon, changing the setting in the PHP.ini did not change anything. Never mind, at least I know where to look . I will ask in some php forum, Thanks anyway.. Cheers
Re: [firebird-support] Converting dates and the ISO-8601 format
Fantastic! Works a treat! Wow, wonder how many other people are going to be tripped up by this ! ? Thanks a lot Mark (and all those who responded)
Re: [firebird-support] Converting dates and the ISO-8601 format
On 9-11-2018 13:38, i...@synapsesoftware.co.uk [firebird-support] wrote:
> I use Firebird primarily with Delphi and have had no problems with using
> and displaying dates (suitable for my country - UK.)
>
> 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).
Disclaimer: I don't use PHP, but a quick google suggested
https://secure.php.net/manual/bg/function.ibase-timefmt.php; that seems
to be a function that has been removed, but one of the comments suggested:
```
/**
* Constant definitions that has been removed from PHP 5
*/
const IBASE_DATE = "ibase.dateformat";
const IBASE_TIME = "ibase.timeformat";
const IBASE_TIMESTAMP = "ibase.timestampformat";
/**
* Sets the format of timestamp, date or time type
* columns returned from queries
*/
function ibase_timefmt($format, $where = IBASE_TIMESTAMP)
{
ini_set($where, $format);
}
```
And https://secure.php.net/manual/en/ibase.installation.php (see first
comment) suggests this can also be configured in the php.ini file.
Mark
--
Mark Rotteveel
Re: [firebird-support] Converting dates and the ISO-8601 format
Thanks very much Helen, a lot for me to digest here . Kind Regards,, Dave.
RE: [firebird-support] Converting dates and the ISO-8601 format
Yeah thanks Alan - I am currently using something similar.. extract(day from COLLECT_DELIVER_DATE)||'/'||extract(month from COLLECT_DELIVER_DATE)||'/'||extract(year from COLLECT_DELIVER_DATE) as COLLECT_DELIVER_DATE Just a bit "wordy" / "verbose" and a bit shocked that you have to go to those lengths to simply format a date field tbh As Helen suggests, I will ask in the PhP forum(s) and see if there is not some magic switch / setting that I am missing to do this "out of the box" or alternatively - a custom library perhaps (though I am loathe to introduce custom DLL;s as it complicates installation.) Thanks for the feedback.. Cheers!
RE: [firebird-support] Converting dates and the ISO-8601 format
>>I use Firebird primarily with Delphi and have had no problems with using and >>displaying dates >>(suitable for my country - UK.) You can use this in your select statements for php: f_dayofmonth(CREATEDATE)||'.'||f_month(CREATEDATE)||'.'||f_year(CREATEDATE) CREATEDATE format the PHP as you wish.
Re: [firebird-support] Converting dates and the ISO-8601 format
i...@synapsesoftware.co.uk 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/ 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
>Programresult
>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).
>DBeaverFormatted 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/
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 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
Re: [firebird-support] Converting dates and the ISO-8601 format
09.11.2018 13:38, i...@synapsesoftware.co.uk [firebird-support] wrote: > I really dont know how to format the results so that they appear "normal" for > my region - > ie the dd/mm/ format. I also am not sure where the problem originates. Formatting of dates is a client-side job. You should go to a PHP forum and ask there. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
