Php oracle формат даты
Join 100's of people reading J Talk's each month to learn about code, devops, entrepreneurship and much more.
Both PHP and Oracle provide functionality manipulating dates and times. Which to use and when?
If you’re new to PHP or Oracle, working out how to handle dates efficiently can be tricky. You may have strategies, which you’ve applied successfully on other platforms, but will they fit to the combination of Oracle and PHP?
This Oracle+PHP recipe should help you understand the functionality, available in both PHP and Oracle, for working with dates and times and how they relate to each other. In doing so, it should help you decide where to draw line in deciding which technology will handle what, and answer questions like “Do I calculate the difference between these two dates in PHP or Oracle?”
Dates and Times in Oracle
Oracle provides three data types for storing date/time values:
- The DATE type, which represents a date and time. A value stored in a DATE field contains “components” corresponding to the century, year, month, day, hour, minute and second. Dates can be anywhere in the range from January 1, 4712 B.C., to December 31, 9999 A.D.
- The TIMESTAMP type, available since Oracle9i, is effectively an extended form of the DATE type and complies with ANSI SQL. It provides greater precision in time, supporting fractions of a second up to nine places and is also capable of storing time zone information.
- The INTERVAL type, since Oracle9i, which supports storage of a time difference such as “two years and five months” or “three days, 18 hours and 45 minutes” and can be summed with a DATE or TIMESTAMP to produce a new DATE / TIMESTAMP value.
The focus here will be on the DATE type, although much that applies to DATE also applies to TIMESTAMP. (For more background about TIMESTAMP and INTERVAL types, read Jonathan Gennick’s Oracle Magazine articles ” Datetime Datatypes Add Precision” and ” Finding the Time in Between” (both published in the Nov.-Dec. 2002 issue).
How Oracle Stores DATEs. The first thing to grasp about the DATE type in Oracle is that its internal representation allows it to be displayed and manipulated in many different ways. It is effectively independent of any specific string format. If you SELECT a DATE type, Oracle automatically converts it to readable string, but this is not how the value is actually been stored.
Selecting the current system time using SYSDATE, which returns a value of type DATE and is the current date and time set for the operating system on which the database resides :
The format is controlled by the Oracle parameterNLS_DATE_FORMAT, and can changed on a session basis (see below). To get a feeling for the internal representation:
Comma-separated values in the result correspond to the bytes Oracle uses to store each component of a date and time, from century down to second One important note here; when comparing DATE types, all the DATE’s components will be compared, down to the seconds. In some cases you may want to compare two dates on a different basis, such as the year, month or day. In such cases functions like TRUNC can be useful to round down the hours, minutes and seconds components of the two DATEs you are comparing. See ” Date Arithmetic” below for more detail.
If you’re familiar with OOP, it may also be helpful to think of DATE types as objects. They possess both properties (year, month, hour etc.) and behavior, such as
This returns the date seven days ago. Further “behavior” includesDATE comparisons, which implies you canSORT BY,GROUP BY, find datesBETWEEN and so on, and subtraction: subtract oneDATE from another to get the integer difference in days (or anINTERVAL type, when usingTIMESTAMP values).
Converting Between DATE Types and Strings. The TO_DATE() and TO_CHAR() functions are used to convert between Oracle DATE”objects” and human readable date strings. Both functions take three arguments; the value to convert, an optional format mask and an optional string identify a language (e.g. FRENCH). Conceptually the format mask is similar to a regular expression; you specify a pattern for a date, which tells Oracle how to relate a matching string to a DATE type. The format mask is described in the Oracle Database SQL Reference under ” Format Models.”
Working with TO_CHAR. Here’s a simple example, again using the SYSDATE function:
Looking at format mask in detail, the’YYYY’ denotes a four-digit year,’MM’ a two digit month,’DD’ a two-digit day of the month,’HH24′ the hours in a 24-hour clock,’MI’ the minutes between 0 and 59, and’SS’ the seconds between 0 and 59. Note the following characters are passed from the format mask into the output “as-is”:
Further strings can be “passed through” by enclosing them in quotes:
There are many more format mask patterns available to address a wide range of use cases, as you will find the documentation.
Note: TO_CHAR can also be used with TIMESTAMP types.
Working with TO_DATE. Oracle can parse strings into DATE types, using the same format masks as TO_CHAR. Given a string like 20050726173102:
Or to convert “Jul 26, 2005 17:13:05”, I can use:
Note: ForTIMESTAMP types the equivalent function isTO_TIMESTAMP.
Changing the default date format. Oracle displays DATE types, by default, according to the format mask defined in the NLS_DATE_FORMAT parameter. This can be changed in the session like:
Date arithmetic. To determine the date six days after the 26th July, 2005, I simply add the value 6 to theDATE object:
The smallest whole unit for this type of operation is a single day. To subtract 18 hours, I need the appropriate fraction of a day:
Similarly, to add 59 seconds:
To work in months or years, given that neither can be expressed in a constant number of days (bearing in mind given leap years and months with different numbers of days), you need theADD_MONTHS function. To add twelve months to a date:
Note: To subtract months, use a negative sign.
The LAST_DAY function returns the last day of the month for a DATE type:
TheTRUNC function rounds down theDATE, according to the date mask it is provided as the second argument. You might use it when makingDATE comparisons where you want to eliminate units such as seconds and minutes from the comparison:
If no date mask is provided,TRUNC will round down theDATE to the start of the day it represents.
Other date-related functions include MONTHS_BETWEEN, for the integer difference in months between two DATE types, NEXT_DAY, to obtain a DATE type of the next week day matching a string literal (for example, ‘MONDAY’), and ROUND, similar to TRUNC, but returning the nearest DATE rounded up or down.
Building SQL Statements using Dates
Some simple examples of using the DATE type in an SQL statement, using the “emp” (employees) table (part of the sample data that comes with an Oracle installation). The “hiredate” column of the “emp” table stores values using the DATE type.
Locating all employees hired between two dates:
Adding a new employee:
Finding all employees who have been with the company for more than 15 years, using anINTERVAL type returned form theTO_YMINTERVAL function:
Dates and Times in PHP
A list of all available functions, based around the UNIX timestamp, can be found in the PHP Manual in the Date and Time Functions section. The focus here will be the date() and mktime() functions, which will typically be the functions you use most. Note that PHP version 5.1.x introduces additional date related functionality, while the examples here restrict themselves to operations available in earlier PHP versions 4.3.x and 5.0.x, although they are also forwards compatible with PHP 5.1.x.
The raw material of dates and times in PHP is the UNIX timestamp; the number of seconds before or after the UNIX epoch, which occurred at 00:00:00 UTC (GMT) on the January 1, 1970. To see a UNIX timestamp, simply print the result of PHP’s time() function; which is equivalent to Oracle’s SYSDATE:
To add or subtract units of days, hours, minutes, you convert the unit to seconds and apply it directly to the timestamp. Performing arithmetic using units of month and year requires the mktime() function (see below).
Formatting UNIX timestamps. The date() function is used to format UNIX timestamps:
The masks for the date() function are well documented in the manual. Any characters thatdate() does not recognize are automatically “passed though” into the output, such as punctuation characters. Characters, which could be mistaken for part of the format, can be escaped with a backslash, for example:
Resulting in “Tuesday the 26th of July.”
Note: If you place the format mask inside double quotes you may need a double backslash to escape certain characters; see the PHP Manual on string types and double quotes for details.
The date() function can also be useful for certain calculations:
Note also the gmdate() function, which is almost exactly the same as thedate() function except that it converts the result to Greenwich Mean Time (UTC).
Creating and manipulating UNIX timestamps. The mktime() function is used to generate a UNIX timestamp from a date, given integers values which represent the components of the date. You could regard mktime() as the parallel to Oracle’s TO_DATE function. For example:
Themktime() function takes care of adjustments required when boundaries, such as that between months are crossed:
Here, attempting to givemktime()”July 37″ results in the correct adjustment into August.
To find the last day of a given month, you can use the proceeding month along with a zero value as the day of that month. For example:
As with the date() function, there’s also a gmmktime() function which also adjusts to GMT.
Problems with dates and time in PHP. One limitation of UNIX timestamps is they are tied to the 32-bit limit of today’s mainstream CPU’s and operating systems. It means you can only represent a range of dates up to the year 2038 and back to 1902 on UNIX platforms. If this limitation poses a problem for your application, you may find the PEAR Date library a useful, albeit slower, alternative.
Another problem area is localization; the date() function only supports English weekday and month names. You may find the easiest practical solution, if you only need to support a handful of languages, is to translate the names using associative arrays.
Note that work is in progress to enhance PHP’s native date and time functions. You’ll find this outlined by Derick Rethans, one of the core PHP developers, in his PHP Time Handling talk which he gave at ApacheCON 2005 in Germany.
Drawing the Line
So the question is where do you draw the line? Where do you place the responsibility for handling date related operations? In PHP or Oracle? This section discusses the options, to help you make informed decisions.
In general, Oracle’s date facilities are more powerful than PHP’s, allowing greater flexibility when it comes to parsing date strings or formatting date output, thanks to a wider range of date formatting masks. Add to that the 32-bit limitation of the UNIX timestamp, that TIMESTAMP can store time zones and localization issues and you may consider passing off all date related work to Oracle. It should be pointed out though that many Web applications, particularly intranet applications, only target a single locale region, so operating within a single language and time zone. Also the range of a UNIX timestamp is often more than enough of the type of data the application will be handling. In such cases, PHP’s date time functions should pose no problems.
Date storage. Considering date and time storage, with some databases it may be necessary to use UNIX timestamps such as PHP5’s built-in SQLite. With Oracle it’s better to use DATE or TIMESTAMP as your column types, for storage for data and time values. The supporting functionality makes date operations easy, particularly when SELECTing based on DATEs and TIMESTAMPs. You’ll also find visually checking dates becomes much easier, as tools like SQL*Plus automatically display dates in a human readable form.
Date formatting. One argument against Oracle’s date formatting capabilities, when thinking in terms of a layered architecture, is that formatting output in your application’s data storage layer is “bad practice”, this being the job of the presentation layer.
If you’re in the (unusual) position of writing an application to run against multiple database implementations, this may be a valid argument. In such cases you may well want to investigate what John Lim has provided ADOdb to help with this problem—see his “Tips on Writing Portable SQL”, “Data Types” section.
More commonly, you’ll be developing against a single database, so vendor abstractions won’t be an issue. You might want to consider having Oracle format a date for you in a variety of ways, rather than trying to massage dates in PHP, as the following query suggests:
Alternatively (or in addition), returning a UNIX timestamp as part of the result fits nicely with PHP’s date() function (see below). Some of Oracle’s format masks, such as “Month”, pads the output with space characters so using date() in such instances may mean fewer lines of code.
Converting Oracle DATE types and Unix Timestamps. The following two Oracle functions implement this for DATE types.
To convert a UNIX timestamp into an Oracle DATE type:
The following PHP script shows how this might be used. Note that this script requires PHP 5.x+, as it uses the new OCI extension function names:
In reverse, the following function returns a UNIX timestamp given an OracleDATE type:
It’s now easy to convert the timestamp into a formatted date, using thedate() function as you loop through the result set.
You have now been introduced to (or reminded of) the date time functionality available in both Oracle and PHP. You should now have a foundation for working with dates and times in your Oracle / PHP applications. You should also have a gained a fair idea of the design and implementation decisions you’ll face when handling dates.
If you're new to PHP or Oracle, working out how to handle dates efficiently can be tricky. You may have strategies, which you've applied successfully on other platforms, but will they fit to the combination of Oracle and PHP?
This Oracle + PHP recipe should help you understand the functionality, available in both PHP and Oracle, for working with dates and times and how they relate to each other. In doing so, it should help you decide where to draw line in deciding which technology will handle what, and answer questions like "Do I calculate the difference between these two dates in PHP or Oracle?"
Dates and Times in Oracle
Oracle provides three data types for storing date/time values:
Downloads for this article:
- The DATE type, which represents a date and time. A value stored in a DATE field contains "components" corresponding to the century, year, month, day, hour, minute and second. Dates can be anywhere in the range from January 1, 4712 B.C., to December 31, 9999 A.D.
- The TIMESTAMP type, available since Oracle9i, is effectively an extended form of the DATE type and complies with ANSI SQL. It provides greater precision in time, supporting fractions of a second up to nine places and is also capable of storing time zone information.
- The INTERVAL type, since Oracle9i, which supports storage of a time difference such as "two years and five months" or "three days, 18 hours and 45 minutes" and can be summed with a DATE or TIMESTAMP to produce a new DATE / TIMESTAMP value.
The focus here will be on the DATE type, although much that applies to DATE also applies to TIMESTAMP . (For more background about TIMESTAMP and INTERVAL types, read Jonathan Gennick's Oracle Magazine articles "Datetime Datatypes Add Precision" and "Finding the Time in Between" (both published in the Nov.-Dec. 2002 issue).
How Oracle Stores DATEs. The first thing to grasp about the DATE type in Oracle is that its internal representation allows it to be displayed and manipulated in many different ways. It is effectively independent of any specific string format. If you SELECT a DATE type, Oracle automatically converts it to readable string, but this is not how the value is actually been stored.
Selecting the current system time using SYSDATE, which returns a value of type DATE and is the current date and time set for the operating system on which the database resides :
SELECT SYSDATE FROM dual /* e.g. 25-JUL-05 */
The format is controlled by the Oracle parameter NLS_DATE_FORMAT , and can changed on a session basis (see below). To get a feeling for the internal representation:
SELECT DUMP(SYSDATE) FROM dual /* e.g. Typ=13 Len=8: 213,7,7,25,23,7,15,0 */
Comma-separated values in the result correspond to the bytes Oracle uses to store each component of a date and time, from century down to second One important note here; when comparing DATE types, all the DATE 's components will be compared, down to the seconds. In some cases you may want to compare two dates on a different basis, such as the year, month or day. In such cases functions like TRUNC can be useful to round down the hours, minutes and seconds components of the two DATE s you are comparing.
See "Date Arithmetic" below for more detail.
If you're familiar with OOP, it may also be helpful to think of DATE types as objects. They possess both properties (year, month, hour etc.) and behavior, such as
This returns the date seven days ago. Further "behavior" includes DATE comparisons, which implies you can SORT BY, GROUP BY, find dates BETWEEN and so on, and subtraction: subtract one DATE from another to get the integer difference in days (or an INTERVAL type, when using TIMESTAMP values).
Converting Between DATE Types and Strings. The TO_DATE() and TO_CHAR() functions are used to convert between Oracle DATE "objects" and human readable date strings. Both functions take three arguments; the value to convert, an optional format mask and an optional string identify a language (e.g. FRENCH ). Conceptually the format mask is similar to a regular expression; you specify a pattern for a date, which tells Oracle how to relate a matching string to a DATE type. The format mask is described in the Oracle Database SQL Reference under "Format Models."
Working with TO_CHAR . Here's a simple example, again using the SYSDATE function:
SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM dual /* e.g. 2005-07-26 17:34:04 */
Looking at format mask in detail, the 'YYYY' denotes a four-digit year, 'MM' a two digit month, 'DD' a two-digit day of the month, 'HH24' the hours in a 24-hour clock, 'MI' the minutes between 0 and 59, and 'SS' the seconds between 0 and 59. Note the following characters are passed from the format mask into the output "as-is":
Further strings can be "passed through" by enclosing them in quotes:
SELECT TO_CHAR(SYSDATE, '"The time is now " HH24:MI:SS "precisely"') FROM dual /* e.g. The time is now 17:38:22 precisely
There are many more format mask patterns available to address a wide range of use cases, as you will find the documentation.
Note: TO_CHAR can also be used with TIMESTAMP types.
Working with TO_DATE. Oracle can parse strings into DATE types, using the same format masks as TO_CHAR . Given a string like 20050726173102:
Note: For TIMESTAMP types the equivalent function is TO_TIMESTAMP .
Changing the default date format. Oracle displays DATE types, by default, according to the format mask defined in the NLS_DATE_FORMAT parameter. This can be changed in the session like:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
Date arithmetic.To determine the date six days after the 26th July, 2005, I simply add the value 6 to the DATE object:
The smallest whole unit for this type of operation is a single day. To subtract 18 hours, I need the appropriate fraction of a day:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) - (1/24 * 18) FROM dual /* e.g. 2005-07-25 23:31:02 */
Similarly, to add 59 seconds:
SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) + (1/(24*60*60) * 59) FROM dual
To work in months or years, given that neither can be expressed in a constant number of days (bearing in mind given leap years and months with different numbers of days), you need the ADD_MONTHS function. To add twelve months to a date:
SELECT ADD_MONTHS( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ), 12) FROM dual /* e.g. 2006-07-26 17:31:02 */
Note: To subtract months, use a negative sign.
The LAST_DAY function returns the last day of the month for a DATE type:
SELECT LAST_DAY( TO_DATE( '20050701', 'YYYYMMDD' ) ) FROM dual /* e.g. 2005-07-31 00:00:00 */
The TRUNC function rounds down the DATE , according to the date mask it is provided as the second argument. You might use it when making DATE comparisons where you want to eliminate units such as seconds and minutes from the comparison:
SELECT TRUNC( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ), 'DD' ) FROM dual /* e.g. 2005-07-26 00:00:00 */
If no date mask is provided, TRUNC will round down the DATE to the start of the day it represents.
Other date-related functions include MONTHS_BETWEEN, for the integer difference in months between two DATE types, NEXT_DAY , to obtain a DATE type of the next week day matching a string literal (for example, 'MONDAY' ), and ROUND, similar to TRUNC , but returning the nearest DATE rounded up or down.
Building SQL Statements using Dates
Some simple examples of using the DATE type in an SQL statement, using the "emp" (employees) table (part of the sample data that comes with an Oracle installation). The "hiredate" column of the "emp" table stores values using the DATE type.
Locating all employees hired between two dates:
Finding all employees who have been with the company for more than 15 years, using an INTERVAL type returned form the TO_YMINTERVAL function:
Dates and Times in PHP
A list of all available functions, based around the UNIX timestamp, can be found in the PHP Manual in the Date and Time Functions section. The focus here will be the date() and mktime() functions, which will typically be the functions you use most. Note that PHP version 5.1.x introduces additional date related functionality, while the examples here restrict themselves to operations available in earlier PHP versions 4.3.x and 5.0.x, although they are also forwards compatible with PHP 5.1.x.
The raw material of dates and times in PHP is the UNIX timestamp; the number of seconds before or after the UNIX epoch, which occurred at 00:00:00 UTC (GMT) on the January 1, 1970. To see a UNIX timestamp, simply print the result of PHP's time() function; which is equivalent to Oracle's SYSDATE :
To add or subtract units of days, hours, minutes, you convert the unit to seconds and apply it directly to the timestamp. Performing arithmetic using units of month and year requires the mktime() function (see below).
Formatting UNIX timestamps. The date() function is used to format UNIX timestamps:
The masks for the date() function are well documented in the manual. Any characters that date() does not recognize are automatically "passed though" into the output, such as punctuation characters. Characters, which could be mistaken for part of the format, can be escaped with a backslash, for example:
Resulting in "Tuesday the 26th of July."
Note: If you place the format mask inside double quotes you may need a double backslash to escape certain characters; see the PHP Manual on string types and double quotes for details.
The date() function can also be useful for certain calculations:
Note also the gmdate() function, which is almost exactly the same as the date() function except that it converts the result to Greenwich Mean Time (UTC).
Creating and manipulating UNIX timestamps. The mktime() function is used to generate a UNIX timestamp from a date, given integers values which represent the components of the date. You could regard mktime() as the parallel to Oracle's TO_DATE function. For example:
The mktime() function takes care of adjustments required when boundaries, such as that between months are crossed:
Here, attempting to give mktime() "July 37" results in the correct adjustment into August.
To find the last day of a given month, you can use the proceeding month along with a zero value as the day of that month. For example:
As with the date() function, there's also a gmmktime() function which also adjusts to GMT.
Problems with dates and time in PHP. One limitation of UNIX timestamps is they are tied to the 32-bit limit of today's mainstream CPU's and operating systems. It means you can only represent a range of dates up to the year 2038 and back to 1902 on UNIX platforms. If this limitation poses a problem for your application, you may find the PEAR Date library a useful, albeit slower, alternative.
Another problem area is localization; the date() function only supports English weekday and month names. You may find the easiest practical solution, if you only need to support a handful of languages, is to translate the names using associative arrays.
Note that work is in progress to enhance PHP's native date and time functions. You'll find this outlined by Derick Rethans, one of the core PHP developers, in his PHP Time Handling talk which he gave at ApacheCON 2005 in Germany.
Drawing the Line
So the question is where do you draw the line? Where do you place the responsibility for handling date related operations? In PHP or Oracle? This section discusses the options, to help you make informed decisions.
In general, Oracle's date facilities are more powerful than PHP's, allowing greater flexibility when it comes to parsing date strings or formatting date output, thanks to a wider range of date formatting masks. Add to that the 32-bit limitation of the UNIX timestamp, that TIMESTAMP can store time zones and localization issues and you may consider passing off all date related work to Oracle. It should be pointed out though that many Web applications, particularly intranet applications, only target a single locale region, so operating within a single language and time zone. Also the range of a UNIX timestamp is often more than enough of the type of data the application will be handling. In such cases, PHP's date time functions should pose no problems.
Date storage. Considering date and time storage, with some databases it may be necessary to use UNIX timestamps such as PHP5's built-in SQLite. With Oracle it's better to use DATE or TIMESTAMP as your column types, for storage for data and time values. The supporting functionality makes date operations easy, particularly when SELECT ing based on DATE s and TIMESTAMP s. You'll also find visually checking dates becomes much easier, as tools like SQL*Plus automatically display dates in a human readable form.
Date formatting. One argument against Oracle's date formatting capabilities, when thinking in terms of a layered architecture, is that formatting output in your application's data storage layer is "bad practice", this being the job of the presentation layer.
If you're in the (unusual) position of writing an application to run against multiple database implementations, this may be a valid argument. In such cases you may well want to investigate what John Lim has provided ADOdb to help with this problem—see his "Tips on Writing Portable SQL", "Data Types" section.
More commonly, you'll be developing against a single database, so vendor abstractions won't be an issue. You might want to consider having Oracle format a date for you in a variety of ways, rather than trying to massage dates in PHP, as the following query suggests:
Alternatively (or in addition), returning a UNIX timestamp as part of the result fits nicely with PHP's date() function (see below). Some of Oracle's format masks, such as "Month", pads the output with space characters so using date() in such instances may mean fewer lines of code.
Converting Oracle DATE types and Unix Timestamps. The following two Oracle functions implement this for DATE types.
To convert a UNIX timestamp into an Oracle DATE type:
The following PHP script shows how this might be used. Note that this script requires PHP 5.x+, as it uses the new OCI extension function names:
In reverse, the following function returns a UNIX timestamp given an Oracle DATE type:
It's now easy to convert the timestamp into a formatted date, using the date() function as you loop through the result set.
You have now been introduced to (or reminded of) the date time functionality available in both Oracle and PHP. You should now have a foundation for working with dates and times in your Oracle / PHP applications. You should also have a gained a fair idea of the design and implementation decisions you'll face when handling dates.
This chapter discusses global application development in a PHP and Oracle Database Express environment. It addresses the basic tasks associated with developing and deploying global Internet applications, including developing locale awareness, constructing HTML content in the user-preferred language, and presenting data following the cultural conventions of the locale of the user.
Building a global Internet application that supports different locales requires good development practices. A locale refers to a national language and the region in which the language is spoken. The application itself must be aware of the locale preference of the user and be able to present content following the cultural conventions expected by the user. It is important to present data with appropriate locale characteristics, such as the correct date and number formats. Oracle Database Express is fully internationalized to provide a global platform for developing and deploying global applications.
This chapter has the following topics:
Establishing the Environment Between Oracle and PHP
Correctly setting up the connectivity between the PHP engine and the Oracle database is first step in building a global application, it guarantees data integrity across all tiers. Most internet based standards support Unicode as a character encoding, in this chapter we will focus on using Unicode as the character set for data exchange.
Zend Core for Oracle is an Oracle OCI application, and rules that apply to OCI also apply to PHP. Oracle locale behavior (including the client character set used in OCI applications) is defined by the NLS_LANG environment variable. This environment variable has the form:
For example, for a German user in Germany running an application in Unicode, NLS_LANG should be set to
The language and territory settings control Oracle behaviors such as the Oracle date format, error message language, and the rules used for sort order. The character set AL32UTF8 is the Oracle name for UTF-8.
For information on the NLS_LANG environment variable, see the Oracle Database Express Edition installation guides.
When Zend Core for Oracle is installed on Apache, you can set NLS_LANG in /etc/profile :
You must restart the Web listener to implement the change.
Manipulating Strings
PHP was designed to work with the ISO-8859-1 character set. To handle other character sets, specifically multibyte character sets, a set of "MultiByte String Functions" is available. To enable these functions, open the Zend Core for Oracle console and go to the Configuration tab.
Navigate to the Extensions subtab and expand the Zend Core Extensions tree control.
Your application code should use functions such as mb_strlen() to calculate the number of characters in strings. This may return different values than strlen() , which returns the number of bytes in a string.
Once you have enabled the mbstring extension and restarted the Web server, several configuration options become available. You can change the behavior of the standard PHP string functions by setting mbstring.func_overload to one of the "Overload" settings.
For more information, see the PHP mbstring reference manual at
Determining the Locale of the User
In a global environment, your application should accommodate users with different locale preferences. Once it has determined the preferred locale of the user, the application should construct HTML content in the language of the locale and follow the cultural conventions implied by the locale.
Developing Locale Awareness
Once the locale preference of the user has been determined, the application can call locale-sensitive functions, such as date, time, and monetary formatting to format the HTML pages according to the cultural conventions of the locale.
When you write global applications implemented in different programming environments, you should enable the synchronization of user locale settings between the different environments. For example, PHP applications that call PL/SQL procedures should map the ISO locales to the corresponding NLS_LANGUAGE and NLS_TERRITORY values and change the parameter values to match the locale of the user before calling the PL/SQL procedures. The PL/SQL UTL_I18N package contains mapping functions that can map between ISO and Oracle locales.
English (United Kingdom)
Encoding HTML Pages
The encoding of an HTML page is important information for a browser and an Internet application. You can think of the page encoding as the character set used for the locale that an Internet application is serving. The browser must know about the page encoding so that it can use the correct fonts and character set mapping tables to display the HTML pages. Internet applications must know about the HTML page encoding so they can process input data from an HTML form.
Instead of using different native encodings for the different locales, Oracle recommends that you use UTF-8 (Unicode encoding) for all page encodings. This encoding not only simplifies the coding for global applications, but it also enables multilingual content on a single page.
Specifying the Page Encoding for HTML Pages
The charset parameter specifies the encoding for the HTML page. The possible values for the charset parameter are the IANA names for the character encodings that the browser supports.
Specifying the Encoding in the HTML Page Header
Use this method primarily for static HTML pages. To specify HTML page encoding in the HTML page header, specify the character encoding in the HTML header as follows:
Specifying the Page Encoding in PHP
This can be found in the Zend Core for Oracle Console in the Configuration tab. Choose the PHP subtab and expand the Data Handling tree control. After entering a value, save the configuration settings and restart the Web server.
This setting does not imply any conversion of outgoing pages. Your application must ensure that the server-generated pages are encoded in UTF-8.
Organizing the Content of HTML Pages for Translation
Making the user interface available in the local language of the user is a fundamental task in globalizing an application. Translatable sources for the content of an HTML page belong to the following categories:
Text strings included in the application code
Static HTML files, images files, and template files such as CSS
Dynamic data stored in the database
Strings in PHP
You should externalize translatable strings within your PHP application logic, so that the text is readily available for translation. These text messages can be stored in flat files or database tables depending on the type and the volume of the data being translated.
Static Files
Static files such as HTML and GIF files are readily translatable. When these files are translated, they should be translated into the corresponding language with UTF-8 as the file encoding. To differentiate the languages of the translated files, stage the static files of different languages in different directories or with different file names.
Data from the Database
Dynamic information such as product names and product descriptions is typically stored in the database. To differentiate various translations, the database schema holding this information should include a column to indicate the language. To select the desired language, you must include a WHERE clause in your query.
Presenting Data Using Conventions Expected by the User
Data in the application must be presented in a way that conforms to the expectation of the user. Otherwise, the meaning of the data can be misinterpreted. For example, the date '12/11/05' implies '11th December 2005' in the United States, whereas in the United Kingdom it means '12th November 2005'. Similar confusion exists for number and monetary formats of the users. For example, the symbol '.' is a decimal separator in the United States; in Germany this symbol is a thousand separator.
Different languages have their own sorting rules. Some languages are collated according to the letter sequence in the alphabet, some according to the number of stroke counts in the letter, and some languages are ordered by the pronunciation of the words. Presenting data not sorted in the linguistic sequence that your users are accustomed to can make searching for information difficult and time consuming.
Depending on the application logic and the volume of data retrieved from the database, it may be more appropriate to format the data at the database level rather than at the application level. Oracle Database XE offers many features that help to refine the presentation of data when the locale preference of the user is known. The following sections provide examples of locale-sensitive operations in SQL.
Oracle Date Formats
The three different date presentation formats in Oracle Database XE are standard, short, and long dates. The following examples illustrate the differences between the short date and long date formats for both the United States and Germany.
Oracle Number Formats
The following examples illustrate the differences in the decimal character and group separator between the United States and Germany.
Oracle Linguistic Sorts
Spain traditionally treats ch , ll as well as ñ as unique letters, ordered after c , l and n respectively. The following examples illustrate the effect of using a Spanish sort against the employee names Chen and Chung.
Oracle Error Messages
The NLS_LANGUAGE parameter also controls the language of the database error messages being returned from the database. Setting this parameter prior to submitting your SQL statement ensures that the language-specific database error messages will be returned to the application.
Consider the following server message:
When the NLS_LANGUAGE parameter is set to French, the server message appears as follows:
For more discussion of globalization support features in Oracle Database Express Edition, see "Working in a Global Environment" in Oracle Database Express Edition 2 Day Developer Guide .
This chapter discusses global application development in a PHP and Oracle Database environment. It addresses the basic tasks associated with developing and deploying global Internet applications, including developing locale awareness, constructing HTML content in the user-preferred language, and presenting data following the cultural conventions of the locale of the user.
Building a global Internet application that supports different locales requires good development practices. A locale refers to a national language and the region in which the language is spoken. The application itself must be aware of the locale preference of the user and be able to present content following the cultural conventions expected by the user. It is important to present data with appropriate locale characteristics, such as the correct date and number formats. Oracle Database is fully internationalized to provide a global platform for developing and deploying global applications.
This chapter has the following topics:
Establishing the Environment Between Oracle and PHP
Correctly setting up the connectivity between the PHP engine and the Oracle database is first step in building a global application, it guarantees data integrity across all tiers. Most internet based standards support Unicode as a character encoding, in this chapter we will focus on using Unicode as the character set for data exchange.
PHP uses the OCI8 extension, and rules that apply to OCI also apply to PHP. Oracle locale behavior (including the client character set used in OCI applications) is defined by the NLS_LANG environment variable. This environment variable has the form:
For example, for a German user in Germany running an application in Unicode, NLS_LANG should be set to
The language and territory settings control Oracle behaviors such as the Oracle date format, error message language, and the rules used for sort order. The character set AL32UTF8 is the Oracle name for UTF-8.
For information on the NLS_LANG environment variable, see the Oracle Database installation guides.
When PHP is installed on Apache, you can set NLS_LANG in /etc/profile :
You must restart the Web listener to implement the change.
Manipulating Strings
PHP was designed to work with the ISO-8859-1 character set. To handle other character sets, specifically multibyte character sets, a set of "MultiByte String Functions" is available. To enable these functions, you must enable the mbstring extension.
Your application code should use functions such as mb_strlen() to calculate the number of characters in strings. This may return different values than strlen() , which returns the number of bytes in a string.
Once you have enabled the mbstring extension and restarted the Web server, several configuration options become available. You can change the behavior of the standard PHP string functions by setting mbstring.func_overload to one of the "Overload" settings.
For more information, see the PHP mbstring reference manual at
Determining the Locale of the User
In a global environment, your application should accommodate users with different locale preferences. Once it has determined the preferred locale of the user, the application should construct HTML content in the language of the locale and follow the cultural conventions implied by the locale.
Developing Locale Awareness
Once the locale preference of the user has been determined, the application can call locale-sensitive functions, such as date, time, and monetary formatting to format the HTML pages according to the cultural conventions of the locale.
When you write global applications implemented in different programming environments, you should enable the synchronization of user locale settings between the different environments. For example, PHP applications that call PL/SQL procedures should map the ISO locales to the corresponding NLS_LANGUAGE and NLS_TERRITORY values and change the parameter values to match the locale of the user before calling the PL/SQL procedures. The PL/SQL UTL_I18N package contains mapping functions that can map between ISO and Oracle locales.
English (United Kingdom)
Encoding HTML Pages
The encoding of an HTML page is important information for a browser and an Internet application. You can think of the page encoding as the character set used for the locale that an Internet application is serving. The browser must know about the page encoding so that it can use the correct fonts and character set mapping tables to display the HTML pages. Internet applications must know about the HTML page encoding so they can process input data from an HTML form.
Instead of using different native encodings for the different locales, Oracle recommends that you use UTF-8 (Unicode encoding) for all page encodings. This encoding not only simplifies the coding for global applications, but it also enables multilingual content on a single page.
Specifying the Page Encoding for HTML Pages
The charset parameter specifies the encoding for the HTML page. The possible values for the charset parameter are the IANA names for the character encodings that the browser supports.
Specifying the Encoding in the HTML Page Header
Use this method primarily for static HTML pages. To specify HTML page encoding in the HTML page header, specify the character encoding in the HTML header as follows:
Specifying the Page Encoding in PHP
This setting does not imply any conversion of outgoing pages. Your application must ensure that the server-generated pages are encoded in UTF-8.
Organizing the Content of HTML Pages for Translation
Making the user interface available in the local language of the user is a fundamental task in globalizing an application. Translatable sources for the content of an HTML page belong to the following categories:
Text strings included in the application code
Static HTML files, images files, and template files such as CSS
Dynamic data stored in the database
Strings in PHP
You should externalize translatable strings within your PHP application logic, so that the text is readily available for translation. These text messages can be stored in flat files or database tables depending on the type and the volume of the data being translated.
Static Files
Static files such as HTML and GIF files are readily translatable. When these files are translated, they should be translated into the corresponding language with UTF-8 as the file encoding. To differentiate the languages of the translated files, stage the static files of different languages in different directories or with different file names.
Data from the Database
Dynamic information such as product names and product descriptions is typically stored in the database. To differentiate various translations, the database schema holding this information should include a column to indicate the language. To select the desired language, you must include a WHERE clause in your query.
Presenting Data Using Conventions Expected by the User
Data in the application must be presented in a way that conforms to the expectation of the user. Otherwise, the meaning of the data can be misinterpreted. For example, the date '12/11/05' implies '11th December 2005' in the United States, whereas in the United Kingdom it means '12th November 2005'. Similar confusion exists for number and monetary formats of the users. For example, the symbol '.' is a decimal separator in the United States; in Germany this symbol is a thousand separator.
Different languages have their own sorting rules. Some languages are collated according to the letter sequence in the alphabet, some according to the number of stroke counts in the letter, and some languages are ordered by the pronunciation of the words. Presenting data not sorted in the linguistic sequence that your users are accustomed to can make searching for information difficult and time consuming.
Depending on the application logic and the volume of data retrieved from the database, it may be more appropriate to format the data at the database level rather than at the application level. Oracle Database offers many features that help to refine the presentation of data when the locale preference of the user is known. The following sections provide examples of locale-sensitive operations in SQL.
Oracle Date Formats
The three different date presentation formats in Oracle Database are standard, short, and long dates. The following examples illustrate the differences between the short date and long date formats for both the United States and Germany.
Oracle Number Formats
The following examples illustrate the differences in the decimal character and group separator between the United States and Germany.
Oracle Linguistic Sorts
Spain traditionally treats ch , ll as well as ñ as unique letters, ordered after c , l and n respectively. The following examples illustrate the effect of using a Spanish sort against the employee names Chen and Chung.
Oracle Error Messages
The NLS_LANGUAGE parameter also controls the language of the database error messages being returned from the database. Setting this parameter prior to submitting your SQL statement ensures that the language-specific database error messages will be returned to the application.
Consider the following server message:
When the NLS_LANGUAGE parameter is set to French, the server message appears as follows:
For more discussion of globalization support features in Oracle Database, see "Working in a Global Environment" in Oracle Database 2 Day Developer's Guide .
In PHP, date('Y-m-d H:i:s') gives result like '2017-02-17 23:10:00' format.
Now, I want to save the result in ORACLE 10g. I am using Oracle 10g Xpress Edition.
Now, I am trying to save the Data like this.
Where $time = date('Y-m-d H:i:s');
The date value is getting saved as 12-Feb-17 . How can I get the date data saved in the desired form?
$time contains the string '2017-02-17 23:10:00', but it gets saved in Oracle as 12-Feb-17? Is this a typo or is this really another day?
I strongly suspect that the value of $time is not what you think it is. Let's have an understanding - when you think you've found a bug in the basic functionality of a widely used piece of software you are probably correct - but the bug is very likely in your understanding of what's going on, not in the software. Given that today is 12-Feb-2017 I think you have to allow for the possibility that $time just might have a value of 12-Feb-2017 .
3 Answers 3
Date in Oracle is not saved as 2017-02-17 23:10:00 or 12-Feb-17 or whatever other format you think. It's stored in a totally different way. When you select the date from the database, the format in which the date is presented is based on your session's NLS_DATE_FORMAT settings.
If you want to get the date in a specific format, you can use TO_CHAR function with the required format:
It seems the OP wanted 'yyyy-mm-dd' (rather than Mon , in particular, but also the order); otherwise this is the right answer.
When you just select the date column, Oracle doesn't show the time component.
What does that mean? When you just select the date, Oracle doesn't show the time component? That depends on the session's NLS_DATE_FORMAT , no?
If you are using SQL/Plus or SQL Developer then it will use the NLS_DATE_FORMAT session parameter to format the date. You can change this using:
(Be aware that this will only change the format in the current session and will not change it for any other sessions/users.)
If you want to give the date a particular format then you will need to convert it to a string.
Interesting. Is that correct about Java/Python? I always assumed (I don't know either of those languages) that they have some sort of date data type, and Oracle will NOT pass strings to them unless requested in the string data type. This is different from SQL*Plus and Toad, which only understand strings. Just wondering (my onw understanding of this may be 100% wrong.)
In Java, the Oracle JDBC driver will store an Oracle date in an oracle.sql.DATE class - this is just a Class representing the binary format Oracle uses internal to the database. If you want to display that to the user then it does not display the bytes, it will use the toString method (or some other similar method invoked by the user) to format the date in a way the user would understand. SQL*Plus and Toad are the same - Oracle just passes 7 bytes and leaves it to something else to format them so the user can understand.
Читайте также: