Different color for each menu item

In a recent project I got a requirement that each menu item should be highlighted in different color when visited. The menu items and their required active colors were:

  • Home – Green
  • Portfolio – Blue
  • Team – Yellow
  • Contact – Red

These colors were to be applied only when that page is being visited otherwise color of menu item should be default black color.

So, if user is visiting home page then menu item should something like this

home_menu.png

And if user is visiting Portfolio page then menu should be something like this

portoflio_menu.png

Considering that this was a WordPress theme project where we were using Understrap as a base theme which is based on Twitter Bootstrap. So, when user visits, for example a home page WordPress will attach a .active CSS class to it. Taking advantage of that we added different classes for each menu item and then used following rule to make menu item colors different:

.navbar-nav > .home.active > a {
    color: green!important;
}
.navbar-nav > .portfolio.active > a {
    color: blue!important;
}
.navbar-nav > .team.active > a {
    color: yellow!important;
}
.navbar-nav > .connect.active > a {
    color: red!important;
}

CSS Class Chaining

We utilized the class chaining method here. If you note that .home.active classes are chained together without space and which means it will select an element with both these classes.
That did the trick and all menu items were in different color.

 

Rollover image – Change image on hover/mouse over

Often when designing websites static or dynamic, PHP or ASP.Net, Laravel or WordPress, you have to design in a way if user hovers an image it gets changed and an alternate image is displayed. This can be easily achieved via simple HTML events. Here is the trick:

<img src="FIRST IMAGE URL GOES HERE"
onmouseover="this.src='SECOND IMAGE URL GOES HERE'"
onmouseout="this.src='FIRST IMAGE URL GOES HERE - AGAIN'" />

It is simple as that.

List of FREE services Startups should be using

I have curated a list of free tools, services, and apps that startups could and in fact should use to grow at the initial stage. Free doesn’t mean they lack quality, instead these free tools are from top-notch companies like RedHat, Google, Asana, GitHub and in all areas from infrastructure to version controlling to marketing and sales to project management.

Have a look at this list here and don’t forget to give your feedback.

I compiled this list a long time ago and recently updated it but it still might have some outdated links that I didn’t get chance to update yet. Feel free to let me know and I’ll update it.

Enjoy!

1 reason why you shouldn’t be an Entrepreneur?

There has been a lot of buzz about entrepreneurship in recent years and lot of people have come up as great entrepreneurs and have set a trend for others to pursue in this direction. This term was not very common in the past and due to which lot of people don’t really know what entrepreneurship really means. Lot of people also mix it with just doing business. That is true somewhat but that means every businessman is an entrepreneur? That is not the case. Lot of people think doing technology enabled business is entrepreneurship. This is also not very true although in our modern era technology has played a big role in business and life.

So, if you try to dig it more, talk to entrepreneurs and other people you will start differentiating business and entrepreneurship. There are different traits of entrepreneurship. Entrepreneur actually take risk and starts a new business on the opportunity he/she finds.

Let me explain. First let’s describe what is opportunity. People come across different problems in life. Opportunity is basically a solution to that problem. You might be thinking how come a problem be an opportunity. You are right and this is where things start to go wrong.

Business is to earn money to fulfill their family needs. While entrepreneurship is finding people’s problems and make money from that problem.

If you hold for a moment and think what entrepreneurs do is ask money from people just to solve their problem. In my opinion this is not a right approach and creates a culture where people instead of helping each other will ask for money to give them a hand or solve their problem. This kills the humanity and one will always thought of making money from others problems.

I won’t say doing business is wrong, but the approach of entrepreneurship is. And this is the one reason you shouldn’t be an entrepreneur if you really cares about people and want to help them.

At the end of the day this is my thought and doesn’t represent anyone. Let me know in comments below if you agree with me or what are your thoughts on doing business and entrepreneurship.

Stop before start – Startup lessons learned!

I have gone through several experiences and ups and downs in my career. One was launching a startup which had a significant impact and reason for me to write this post to share the lessons learned and why we should stop on a starting line for a moment before we run.

Starting line

It was a long day back in February 2014. I was looking at the ecommerce industry of Pakistan and was surprised and happy at the same time that Pakistan ecommerce industry has been growing rapidly in past few years. Payment method was the biggest hurdle in Pakistan for the ecommerce industry and after the introduction of Cash On Delivery (COD) ecommerce market grew exponentially. Logistics companies like TCS, Leopard, and other played a significant role by offering COD services to businesses that eases the operation of shipping and payment for local businesses. That was the aha moment for me and I decided to launch my own ecommerce business.

The ground

I had been developing ecommerce solutions for some time and I had enough experience to run the show and grow it. The question was what am I going to sell?

At that moment I decided to share my idea with my long time pal and cousin. He was also looking for a breakthrough in his life. When I put this idea in front of him he immediately agreed to dive into it. So, we started planning and thinking what should we sell and how to arrange the inventory.

Let me mention here that one big problem with us was funding and our aim was to start with minimum investment. In fact at back of our minds we were thinking to go with zero investment. This was something that I previously had done so it seemed possible with some teamwork, hard work, commitment, and time. We had to wear so many hats at the same time.

Mix of skills, the good thing

Ultimately, we decided to go with apparels to sell. What we actually did we mixed experience and fields of our both. I was expert in technology and specially in ecommerce side and he had experience in selling unstitched cloths. So we decided to go with selling cloths where he will take care of vendors, inventory, shipping (since he was also in transport business so it was best that he handle delivery as well) and related stuff. While I was responsible for the technology side, plus customer service and sales, and digital marketing, and everything that comes in between and belongs to technology.

The launch

We moved forward and in March 2014 we launched our website and started marketing on social media. Let me mention that since my partner was already working he started as a part time. This was important decision. My opinion at that time was if you have enough savings to run your kitchen for ideally 6 months or at least for 2-3 months then we can focus on our business full-time. Because we didn’t have investment so time was our investment. As much time we could put in we had to, in fact we must otherwise I could see what’s going to happen. My partner was already in and there was no going back so we kept moving forward.

I realized early in the stage that we need more team members since we were not able to give full time. On the other hand we were not in a position to hire anyone at this stage due to lack of investment. I decided to include another partner and give him a share and my first partner agreed. I had a perfect guy in my mind and we put the proposal in front of him. Just like my first partner he was also looking for a breakthrough in his life and he also showed interest at first place. He was in and we started working.

I found a vendor which was actually my friend working in a same field and we agreed on doing business with each other. So we had some initial inventory, we started taking photos, doing Photoshop, uploading on website, and started posting on social media. So far so good, but second partner started showing less interest and in May we were back to two partners.

The moment of joy

At the end of May we were rewarded for our hard work and we received our first order. We were excited. We kept our head down and arranged the inventory and shipped the order.

In June, there was more joy for us. We received our first international order and a good one. We were so excited that our primary market was local but I had been planning in a way to expand it internationally and it did work and we got our very first order from abroad. Awesome!

Turning point

failureWe continued and we started getting more orders but turn around time was slow as usually is in starting phases. This was the point where we had to take some more tough decisions but things started to change. We were still not in a position that we both can run our kitchen from the income of our startup. In contrast our business was demanding more time, more efforts, and investment at this stage. None of which we could do since we had to run our kitchen and in parallel support our startup. And we had nothing to put in. Ultimately, we were no where, we were short on inventory, our orders got cancelled, and thus no returning customer and no sale.

Then the day came when we took the biggest decision in July. We decided to wind up and in loss. Fortunately, there wasn’t enough investment so our loss was also not that big and we beared it somehow.

Lessons Learned

We learned lots of lessons. To start a business you must have enough investment to run the show for a year at least including your kitchen. Due to lack of investment and in return lack of marketing, team, resources, suppliers, and time (due to part-time work) we were unable to survive. People can quote you numerous examples and I myself know some but all they have investment in some context. I read an article that how WD-40 was named. It was actually 40th formula that got successful and 39 formulas before that all failed. So, the creator of WD-40 had enough time and money to keep experimenting with his formulas and keep supporting his family and kitchen. That is also investment.

One might think why didn’t we find any investors. Actually, startup and entrepreneurship culture is booming in Pakistan but still there are some hurdles and ecosystem is still not there yet. Good investors are hard to find with profit and loss basis. So we were up to our own.

On the other hand approach and lack of skills was also a major issue. One man cannot do everything so all the partners have to contribute. They should have ‘Can-Do approach’ and should be able to initiate ideas, and know how to execute them. Skills in particular field is also very important. But working in some field doesn’t guarantee that you have skills in that field. At the same time you have to bring and apply your previous experience. Plus, communication skills and self-confidence is a must to achieve something. You should be problem solver too. Unable to think and coming up with no ideas in problem times is one of the biggest problems itself.

WooCommerce Per Product Shipping Price Calculation

If you have different rates depending on the item you will need to setup each one in ‘Flat Rate’ shipping method and make sure it is selected on the Product itself.

First you will need to have a Shipping Class setup:

Go to the left hand menu, under Products you will find Shipping Classes.
Give your Shipping Class a name and a description and then hit “Add New Shipping Class”

Once that is done you can go back to the Shipping Setup, Under WooCommerce Settings.

Click on Flat Rate and at the bottom of the screen click on “Add New Rate”
Choose your Shipping Class.
Give it a cost and a handling fee.

Why will you need separate shipping classes?

Imagine you sell small items but just so happen to have one large item you want to sell on your site. You obviously cannot charge the same as you can with the small items.

What J talked about in 2013

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 37,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 14 sold-out performances for that many people to see it.

Click here to see the complete report.

 

2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 24,000 views in 2012. If each view were a film, this blog would power 6 Film Festivals

Click here to see the complete report.

 

Working with Dates and Times in Oracle and PHP

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 :

SELECT SYSDATE FROM dual /* e.g. 25-JUL-05 */

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:

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 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

SELECT SYSDATE - 7 FROM dual /* e.g. 18-JUL-05 */

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:

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:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) FROM dual

Or to convert “Jul 26, 2005 17:13:05”, I can use:

SELECT TO_DATE('Jul 26, 2005 17:13:05', 'Mon DD, YYYY HH24:MI:SS') FROM dual

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:

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 theDATE object:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) + 6 FROM dual
    /* e.g. 2005-08-01 17:13:05 */

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 theADD_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 */

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:

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 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:

SELECT
    ename, TO_CHAR(hiredate, 'ddth Mon, YYYY')
FROM
    emp
WHERE
    hiredate
BETWEEN
    TO_DATE('1980-01-01','YYYY-MM-DD')
AND
    TO_DATE('1985-01-01','YYYY-MM-DD')
ORDER BY
    hiredate
DESC

Adding a new employee:

INSERT INTO emp
   (
        empno,
        ename,
        job,
        mgr,
        hiredate,
        sal,
        deptno
    )
VALUES
   (
        8002,
        'JOHNSON',
        'ANALYST',
        7566,
        TO_DATE('2005-07-22','YYYY-MM-DD'),
        3000,
        20
    )

Finding all employees who have been with the company for more than 15 years, using anINTERVAL type returned form theTO_YMINTERVAL function:

SELECT
    *
FROM
    emp
WHERE
    SYSDATE - TO_YMINTERVAL('15-00') >  hiredate

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:

<?php
print time(); // e.g. 1122391862
?>

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:

<?php
print date('Y-m-d H:i:s',time()); // e.g. 2005-07-26 17:31:02
?>

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:

<?php
print date('l \t\he jS of F',1122391862); // e.g. Tuesday the 26th of July
?>

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:

<?php
$years = range(2005, 2020);
foreach ( $years as $year ) {
    if ( date('L', mktime(0,0,0,1,1,$year) ) ) {
        print "$year is a leap year\n";
    }
}
?>

Which tells me:
    2008 is a leap year
    2012 is a leap year
    2016 is a leap year
    2020 is a leap year

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:

<?php
$year = 2005;
$month = 7;
$day = 27;
$hour = 12;
$minute = 34;
$second = 43;

print mktime($hour, $minute, $second, $month, $day, $year);
?>

Themktime() function takes care of adjustments required when boundaries, such as that between months are crossed:

<?php
$year = 2005;
$month = 7;
$day = 27 + 10; // Add 10 days
$hour = 12;
$minute = 34;
$second = 43;

print date('Y-M-d H:i:s',mktime($hour, $minute, $second, $month, $day, $year));
    // e.g. 2005-Aug-06 12:34:43
?>

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:

<?php
$year = 2005;
$month = 8; // August
$day = 0; // Last day of July
$hour = 12;
$minute = 34;
$second = 43;

print date('Y-M-d',mktime($hour, $minute, $second, $month, $day, $year));
?>

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:

SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'DD Mon, YYYY') AS hired_pretty,
    TO_CHAR(hiredate, 'DD Mon, YYYY', 'NLS_DATE_LANGUAGE=GERMAN') AS hired_german
FROM
    emp
ORDER BY
    hiredate

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:

CREATE OR REPLACE
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
         * Converts a UNIX timestamp into an Oracle DATE 
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;

        BEGIN

            IF unixts > max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts < min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
                oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
            END IF;

            RETURN (oracle_date);

    END;

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:

<?php
$conn = oci_connect('scott', 'tiger');

$sql = "
    SELECT
        *
    FROM EMP
    WHERE
        hiredate
    BETWEEN
        unixts_to_date(:startdate)
    AND
        unixts_to_date(:enddate)
    ORDER BY
        hiredate
    DESC
    ";

$stmt = oci_parse($conn, $sql);

// Bind a UNIX timestamps to :startdate and :enddate
oci_bind_by_name($stmt, ":startdate", mktime(0,0,0,1,1,1981));
oci_bind_by_name($stmt, ":enddate", mktime(0,0,0,1,1,1990));

oci_execute($stmt);

print "NAME  : HIREDATE\n";
while ( $row = oci_fetch_assoc($stmt) ) {
   print "{$row['ENAME']} : {$row['HIREDATE']}\n";
}
oci_free_statement($stmt);

oci_close($conn);
?>

In reverse, the following function returns a UNIX timestamp given an OracleDATE type:

CREATE OR REPLACE
    FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;

        BEGIN

            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
            END IF;

            RETURN (unix_ts);

        END;

The following query shows how it might be used:SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'ddth Mon, YYYY') AS hired_pretty
    date_to_unixts(hiredate) AS hired_unixts
FROM
    emp
ORDER BY
    hiredate

It’s now easy to convert the timestamp into a formatted date, using thedate() function as you loop through the result set.

Conclusion

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.

[Ref: http://www.oracle.com/technetwork/articles/fuecks-dates-098686.html]