Php oci8 - how is the oracle timestamp date format set
I am fetching data from an Oracle database in a php5 script. I want to return a specific timestamp format. I found answers on SO that say a statement similar to what Oracle describes will do the trick:
$stid = oci_parse ($connection, "alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss.ff3'");
oci_execute ($stid);
which is to ensure that subsequent requests such as
$stid = oci_parse ($connection, 'select * from table');
oci_execute ($stid);
timestamp formats are output, returned in the correct format.
But no matter what I do, I always get formats like
28-NOV-12 05:37:24.000000 PM
So my question is, what happened? What instruction to execute in php to get the desired timestamp formats?
Side question: How can I make sure php is getting the timestamp in ISO 8601 format, so lines like 2012-11-28T17: 37: 24.000Z?
And how to provide 'Z' in this case, hence not strings like 2012-11-28T16: 37: 24.000 + 01: 00?
source to share
ff3
is invalid for the datatype DATE
as it does not contain fractional seconds.
As far as your ISO8601 formatting is concerned, it only TIMESTAMP WITH TIME ZONE
has a real time zone. If you want this to be formatted with Z (i.e. UTC), you can do:
ALTER SESSION SET TIME_ZONE = 'UTC';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"';
Then when you choose TIMESTAMP WITH TIME ZONE
, you will receive it in that format.
If you need to format DATE
or TIMESTAMP
like this, you can just fake a part of the timezone using formats;
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd"T"hh24:mi:ss".000Z"'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
source to share
Usage function: TO_CHAR
Example: to_char (<>, 'YYYY-MM-DD "T" HH24: MI: SS: FF3 "Z"')
Docs:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions200.htm#SQLRF06129
Format models:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34510
source to share