17.2 Dates and Time in PHP
| Up to now you have seen how you can work with dates and time in PostgreSQL. In this section we will see how dates and time can be treated when working with PHP's onboard functions. 17.2.1 PHP's FunctionsIn this section you will be guided through PHP's functions related to time. 17.2.1.1 getdateOne of the most important functions is the getdate function. It returns the current date in an array of values that can be used for further treatment. Let's take a look at an example: <?php $mydate = getdate(); echo "seconds: ".$mydate['seconds']."<br>"; echo "minutes: ".$mydate['minutes']."<br>"; echo "hours: ".$mydate['hours']."<br>"; echo "mday: ".$mydate['mday']."<br>"; echo "wday: ".$mydate['wday']."<br>"; echo "mon: ".$mydate['mon']."<br>"; echo "year: ".$mydate['year']."<br>"; echo "yday: ".$mydate['yday']."<br>"; echo "weekday: ".$mydate['weekday']."<br>"; echo "month: ".$mydate['month']."<br>"; ?> First the current time is computed. In the next step, the values stored in the array are displayed. seconds: 2 minutes: 17 hours: 16 mday: 12 wday: 6 mon: 1 year: 2002 yday: 11 weekday: Saturday month: January 17.2.1.2 date and mktimeSometimes you want a date to be displayed in a certain format. For this purpose, you can use a function called date. Let's take a look at an example: <?php echo date("Y-m-d H:i:s"); ?> The date is displayed in the same way you have already seen when dealing with PostgreSQL: 2002-01-12 21:38:19 However, with the help of date you can do even more. In general the date function is used to format a date to your needs. If you want to format any date, you pass it to the function; otherwise, the current date will be used. Let's see how this can be done: <?php echo date("Y-m-d H:i:s", mktime(23,9,4,12,1,2002))."<br>"; echo date("Y-m-d, h a", mktime(23,9,4,12,1,2002))."<br>"; echo date("Y-m-d, z", mktime(23,9,4,12,1,2002))."<br>"; ?> The first line displays the date passed to the function by computing the result of the mktime function. mktime takes the input parameters and makes a date out of it. The result of this function can be used as input for the date function. In the first example the date including the time is displayed. In the second example you can see that 23 o'clock is equal to 11 p.m. The third example shows how many days in the year have passed until the date passed to the function. If you execute the script, three lines will be displayed: 2002-12-01 23:09:04 2002-12-01, 11 pm 2002-12-01, 334 So far, you have seen the most important flags of the date command. The following list shows all the flags and formats accepted by date:
17.2.1.3 checkdateTo find out if a date is valid, PHP provides a function called checkdate. The function accepts three parameters. The first one defines the month, the second one the day, and the third one the year of the date. When checking the date, leap years are taken into consideration. Let's see how this function can be used: <?php if (checkdate(12, 1, 2002)) { echo "date valid<br>\n"; } else { echo "date invalid<br>\n"; } if (checkdate(12, 1, 200200)) { echo "date valid<br>\n"; } else { echo "date invalid<br>\n"; } ?> The first date is valid, but the second one isn't: date valid date invalid 17.2.1.3 gettimeofdayThe gettimeofday function returns the current date in an array. In contrast to the getdate function, gettimeofday returns microseconds, seconds, the minutes west of Greenwich, and the type of DST (Daylight Saving Time) correction. The next listing contains an example: <?php $mydate = gettimeofday(); echo "sec: ".$mydate['sec']."<br>\n"; echo "usec: ".$mydate['usec']."<br>\n"; echo "minuteswest: ".$mydate['minuteswest']."<br>\n"; echo "dsttime: ".$mydate['dsttime']."<br>\n"; ?> Four lines will be displayed: sec: 1010875489 usec: 567304 minuteswest: -120 dsttime: 0 The third line especially will be important because the content of the third field will help you when working with time zones. In this scenario we are two hours east of Greenwich (two hours in front). 17.2.1.4 gmtimeIf you want a result to be displayed using GMT (Greenwich Mean Time), you can use gmtime instead of the date function. The advantage of this function is that you don't have to retrieve the current time zone and compute the offset to GMT. gmtime can do all these things for you: <?php echo date ("M d Y H:i:s", mktime (22,39,18,12,1,2002))."<br>"; echo gmdate ("M d Y H:i:s", mktime (22,39,18,12,1,2002))."<br>"; ?> If you take a look at the next example, you will find out that the second line differs from the first line: Dec 01 2002 22:39:18 Dec 01 2002 21:39:18 17.2.1.5 gmmktimeThe gmmktime function can be used to compute a Unix timestamp for a GMT date. Let's take a look at the syntax overview of the command: int gmmktime (int hour, int minute, int second, int month, int day, int year [, int is_dst]) To compute the number of seconds passed until Unix starting time, you can use a script like the following: <?php $x = gmmktime (1, 17, 53, 1, 13, 2002); echo "x: $x<br>\n"; ?> More than one billion seconds have passed since Unix starting time: x: 1010884673 17.2.1.6 gmstrftime and strftimeTo format a timestamp according to your needs, PHP provides two additional functions, which will be discussed in this section: gmstrftime and strftime. The difference between the two functions is that gmstrftime returns the data as GMT, whereas strftime uses the local settings to display the time. Let's take a look at an example: <?php setlocale ('LC_TIME', 'en'); echo "GMT:<br>\n"; echo gmstrftime ("%b %d %Y %H:%M:%S", mktime (23, 19, 8 ,13 ,1 ,2002))."<br><br>\n"; echo "local settings:<br>\n"; echo strftime ("%b %d %Y %H:%M:%S", mktime (23, 19, 8 ,13 ,1 ,2002))."<br>\n"; ?> First the local setting is defined. In the next step the date passed to the mktime function is formatted according to the template passed to gmstrftime, and strftime functions as the first parameter. If you execute the script, several lines will be returned as you can see in the next listing: GMT: Jan 01 2003 22:19:08 local settings: Jan 01 2003 23:19:08 17.2.1.7 localtimePHP's localtime function is similar to Perl's localtime function. It returns the current local time as an array consisting of all necessary components: <?php $mydate = localtime(); echo "tm_sec: ".$mydate[0]."<br>\n"; echo "tm_min: ".$mydate[1]."<br>\n"; echo "tm_hour: ".$mydate[2]."<br>\n"; echo "tm_mday: ".$mydate[3]."<br>\n"; echo "tm_mon: ".$mydate[4]."<br>\n"; echo "tm_year: ".$mydate[5]."<br>\n"; echo "tm_wday: ".$mydate[6]."<br>\n"; echo "tm_yday: ".$mydate[7]."<br>\n"; echo "tm_isdst: ".$mydate[8]."<br>\n"; ?> The array returned by PHP can be indexed easily, so it is an easy task to retrieve the data you need. When you execute the script, the array will be displayed line by line: tm_sec: 59 tm_min: 46 tm_hour: 13 tm_mday: 13 tm_mon: 0 tm_year: 102 tm_wday: 0 tm_yday: 12 tm_isdst: 0 17.2.1.8 timePHP's time function returns the current Unix timestamp: <?php echo time(); ?> More than a billion seconds have passed since Unix starting time: 1010932663 17.2.1.9 strtotimeTo convert a string to a timestamp, PHP provides a function called strtotime. Various input formats are accepted by the function, so almost any useful format can be converted to a timestamp. The next example shows a variety of input formats accepted by strtotime: <?php # valid dates: echo "January 13 2002: ".strtotime("January 13 2002")."<br>\n"; echo "January 13 02: ".strtotime("January 13 02")."<br>\n"; echo "2002/1/13: ".strtotime("2002/1/13")."<br>\n"; echo "2002-1-13: ".strtotime("2002-1-13")."<br>\n"; echo "2002/1/13 18:32:17: ".strtotime("2002/1/13 18:32:17")."<br>\n"; echo "2002-1-13 18:32:17: ".strtotime("2002-1-13 18:32:17")."<br>\n"; # abbreviation echo "3 months 12 days 29 seconds: ". strtotime("3 months 12 days 29 seconds")."<br>\n"; # in valid dates: echo "13.1.2001: ".strtotime("13.1.2001"); ?> All formats but the German format you can see in the last line are accepted by PHP. As you have already seen, Unix timestamps are based on seconds. With the help of these simple formats, Unix timestamps are portable and interacting with other software products is easy. When you execute the script, you will see how the result is displayed: January 13 2002: 1010876400 January 13 02: 1010876400 2002/1/13: 1010876400 2002-1-13: 1010876400 2002/1/13 18:32:17: 1010943137 2002-1-13 18:32:17: 1010943137 3 months 12 days 29 seconds: 1019742570 13.1.2001: -1 17.2.1.10 microtimeRetrieving the current timestamp in microseconds, you can use the microtime function as explained in the next listing: <?php $data = microtime(); $comp = split(" ", $data); echo "usec: $comp[0]<br>\n"; echo "sec: $comp[1]<br>\n"; ?> The result of microtime is one string consisting of two components. The first one contains the number of microseconds. The second component contains the seconds that have passed since Unix starting time. If you execute the script in the previous listing, the result could look like this: usec: 0.94283500 sec: 1010936345 17.2.2 PHP and PostgreSQL as a TeamIn the past two sections you have dealt with PHP and PostgreSQL in detail. However, it is important to know how the two software packages can interact with each other to build sophisticated, database-driven applications. In this section we want to introduce you to the world of working with dates and time taken from a PostgreSQL database. As a first example, you will see how to implement a system for listing the result of a race. First, create a table: phpbook=# CREATE TABLE race (id int4, competitor name, racetime time); CREATE The first column contains an id. The second column is used to store the name of a competitor, and the third column stores the time a racer needs. After creating the table, you can add some data to the table. You can use COPY: phpbook=# COPY race FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 Maier 00:01:53.12 >> 2 Jackson 00:02:07.43 >> 3 Bush 00:01:59.09 >> 4 Gates 00:02:43.59 >> 5 Ballmer 00:02:59.06 >> \ . Five records have been added to the table. To retrieve all records, you can use a simple SELECT statement as shown in the next example: phpbook=# SELECT * FROM race; id | competitor | racetime ----+------------+---------- 1 | Maier | 00:01:53 2 | Jackson | 00:02:07 3 | Bush | 00:01:59 4 | Gates | 00:02:43 5 | Ballmer | 00:02:59 (5 rows) The problem with this result is that the number of microseconds is not returned and cannot be found in the result. To get around the problem, you can cast the third column to interval as we have done in the next listing: phpbook=# SELECT id, competitor, racetime::interval as racetime FROM race; id | competitor | racetime ----+------------+------------- 1 | Maier | 00:01:53.12 2 | Jackson | 00:02:07.43 3 | Bush | 00:01:59.09 4 | Gates | 00:02:43.59 5 | Ballmer | 00:02:59.06 (5 rows) Now that you have seen what is in the database, you can start writing a script that displays a list of all competitors and their delay: <?php # connecting to the database $dbh = pg_connect("dbname=phpbook user=postgres"); if (!$dbh) { echo "connection to the database cannot be established<br>\n"; exit; } # retrieving data $sql = "SELECT id, competitor, racetime::interval AS racetime FROM race ORDER BY racetime"; $res = pg_exec($dbh, $sql); if (!$res) { echo "data cannot be retrieved<br>\n"; exit; } $rows = pg_numrows($res); echo "$rows competitors found<br><br>\n"; # if no competitors -> exit if ($rows == 0) exit; # retrieving time of winner and displaying information $winner = pg_fetch_row($res, 0); $winsec = postgres2php($winner[2]); echo "$winner[1]:<br>\n"; # processing other competitors for ($i = 1; $i < $rows; $i++) { $comp = pg_fetch_row($res, $i); $compsec = postgres2php($comp[2]); $diff = $compsec - $winsec; printf("%s %4.2f<br>\n", $comp[1], $diff); } # transforming PostgreSQL data to PHP data function postgres2php($data) { $data = split("\.", $data); $sec = strtotime($data[0]) + $data[1]/100; return $sec; } ?> After connecting to the database, you can select the data from the table. The first record retrieved from the result is stored in $winner. To convert the data returned by PostgreSQL to a format that can be used by PHP, postgres2php is called. postgres2php has been implemented at the end of the script. First the data passed to the function is split, and in the next step, the first part of the array generated by splitting the input is converted to a timestamp. The main problem with PHP and PostgreSQL is that PHP's timestamps are not as precise as PostgreSQL's data types. Therefore you can simply add 1% of the second component in the array to the timestamp. This way it is possible to use a double instead of an integer value to store the data. The problem with dealing with dates and time this way is that a double that has been generated using the way you saw in this example cannot be used in combination with PHP's time functions. However, working with doubles is an easy way to compare various dates with each other. After processing the first record, the rest of the data is processed with the help of a simple for loop. The delay of the various competitors is computed and the name as well as the delay are displayed on the screen. To display the number on-screen, printf is used. Some C programmers among you might already have dealt with printf in detail. printf is used to display data in a formatted way. In this scenario the first variable is displayed as string. The second variable will be treated as a floating-point number. When you execute the script, the result will be displayed on screen: 5 competitors found Maier: Bush 5.97 Jackson 14.31 Gates 50.47 Ballmer 65.94 As you can see, Jackson is about 14 seconds behind Maier, who has won the race. In this example you saw that most of the computations have been performed with the help of the PHP script. However, in some cases it can be an advantage to make PostgreSQL do some things for you. The next piece of code shows how almost the same result can be computed using SQL code: BEGIN; SELECT id, competitor, NULL AS racetime, 0 AS flag INTO TEMPORARY tmptab FROM race WHERE racetime=(SELECT min(racetime)::interval FROM race) UNION SELECT id, competitor, racetime::interval- (SELECT min(racetime)::interval FROM race) AS racetime, 1 AS flag FROM race WHERE racetime > (SELECT min(racetime)::interval FROM race); SELECT id, competitor, racetime FROM tmptab ORDER BY flag, racetime; COMMIT; First a transaction is started. In the next step a temporary table is created that will contain the id and the name of a competitor as well as the distance between the various competitors. After creating the temporary table, all records are selected from it. The result must be ordered by flag and racetime. The flag field has been introduced to make sure that Maier is not displayed at the end of the result. Usually NULL values will be added at the end of a result flag makes sure that this won't happen. If you execute the script, you will see what comes out: [hs@duron hs]$ psql phpbook < sql.sql SELECT id | competitor | racetime ----+------------+------------- 1 | Maier | 3 | Bush | 00:00:05.97 2 | Jackson | 00:00:14.31 4 | Gates | 00:00:50.47 5 | Ballmer | 00:01:05.94 (5 rows) As we have promised, the output of the SQL batch job is pretty similar to the one you have generated with the help of PHP. Let's take a closer look at the competitor called Ballmer: In the preceding listing, you can see that he is 1 minute and 5.94 seconds behind. If you take a look at the result generated by PHP, you will see that he is 65.94 seconds behind. The result is the same, but it is displayed differently. To display a result in the same way as you would with PostgreSQL is slightly more difficult when working with PHP because microseconds have to be treated differently and processed separately in order to have the opportunity to work with PHP's onboard functions. Especially when performing computations with microseconds involved, these details can be annoying. As you have seen in this section, working with dates and time can be done with PHP as well as PostgreSQL. However, both systems have advantages but also some disadvantages. Usually it is enough to implement a set of converters to parse the date or time you have to process. In some cases, however, it can be more comfortable to perform simple calculations within the database because displaying the data is easier this way. |
EAN: N/A
Pages: 201