This batch script will backup any MySql database on the local machine, including all routines (stored procedures and functions), to a file named after the database followed by the date (YYYY-MM-DD) and time (HHMMSSSS). The first argument is the database name, second is the username to run the backup as and third is the password.

This script requires your date and time formats to be the windows default formats. Changing them can break this script. The script pauses at the end to cause a cmd window to remain open for long enough for the user to read the output.

   1  
   2  @echo off
   3  echo Starting Backup of Database: %1
   4  
   5  For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set dt=%%c-%%a-%%b)
   6  For /f "tokens=1-4 delims=:." %%a in ('echo %time%') do (set tm=%%a%%b%%c%%d)
   7  set bkupfilename=%1 %dt% %tm%.bak
   8  echo Backing up to file: %bkupfilename%
   9  
  10  mysqldump %1 --routines -u %2 -p%3 > "%bkupfilename%"
  11  
  12  echo Backup Complete!
  13  pause
  14  echo on

--
Version 0.1.0 - 2006-03-13
STEM: The STEM Cells of PHP
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License
http://creativecommons.org/licenses/by-sa/2.5/

This script shows, by example, how to convert from a UNIX Timestamp (the returned data type from the PHP time() or mktime() functions) into a string that can be stored in MySql's DATETIME or TIMESTAMP fields. It also shows how to convert back into a UNIX Timestamp. The times are stored in the database as GMT/UTC times, so finally, it shows how to convert from the resulting UNIX timestamp into a string that represents the time with a given GMT/UTC offset.

Create the UNIX Timestamp, using the current system time.

   1  
   2  // Create the UNIX Timestamp, using the current system time
   3  $tUnixTime = time();

Convert that UNIX Timestamp into a string, safe for MySql. The string will be in the format CCYY-MM-DD HH:MM:SS and will represent the GMT/UTC time represented by the UNIX Timestamp. Example: 2006-02-10 20:33:55

   1  
   2  // Convert that UNIX Timestamp into a string (GMT), safe for MySql
   3  $sGMTMySqlString = gmdate("Y-m-d H:i:s", $tUnixTime);

The string created above is suitable for sending to MySql and storing in a DATETIME or TIMESTAMP field. This snippet does not include the code that would store and retrieve the data.

Parse the String and store the result in a new UNIX Timestamp. Because strtotime() thinks that any string passed to it, that does not specify a timezone, is in the local timezone, according to the settings in your configuration, we append " GMT" to the string that is returned from MySql. This creates a new string that is both compliant with strtotime() and explicitly a GMT value.

   1  
   2  // Parse the String into a new UNIX Timestamp
   3  $tParsedTime = strtotime($sGMTMySqlString . " GMT");

Show the two values, in W3C format, for debugging. W3C format dates include the full date, time and timezone information. If we visually or programmatically compare the two values, they should be identical in all aspects.

   1  
   2  // Show the Original Time and Parsed Time on the screen, in W3C Format
   3  print "Original Time: " . date(DATE_W3C, $tUnixTime);
   4  print "Parsed Time: " . date(DATE_W3C, $tParsedTime);

The following two lines demonstrate how to take one of these UNIX timestamps and convert it into a user-friendly string, using a time offset. Typically, you would store this time offset in your user's profile.

   1  
   2  // Create a String showing the DateTime in CCYY-MM-DD Format in a Specified Zone
   3  $fUTCOffset = +2.00; // GMT/UTC Offset in Hours (2.50 = 2 hours 30 minutes)
   4  print "Time in GMT+2: " . gmdate("Y-m-d H:i:s", $tUnixTime + $fUTCOffset * 3600);

I am not sure whether my idea to use a floating point number to represent the user's time zone is correct. I must check the standard. If the standard is to use a time-span value (2.50 is not 2 hours 30 minutes but 2 hours and 50 minutes, 2.30 is 2 hours and 30 minutes) I will change it. This is a minor change.

I did not know of a PHP function that would convert a GMT/UTC UNIX Timestamp into a string representing a time in a specified zone. All I could find is the date() function which uses the current zone according to the config and the gmdate() zone which returns GMT/UTC. To implement the functionality, I modified the date by adding to it and then converting to GMT/UTC. I do not like this, but it works.

This code makes no attempt to guess whether the current user is affected by DST or not. It simply uses an offset from GMT/UTC. According to my research, DST guessing is dodgy at best and, according to some, soon to be outdated by a change to the DST system in America.

I am not sure, even if I knew how, that I would trust built-in time zone functionality. I would suspect its accuracy for the same reasons I don't use automatic guessing of DST status.

--
Version 0.0.1 - 2006-02-10
Stephen's Modules: DateTime with PHP and MySql
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License
http://creativecommons.org/licenses/by-sa/2.5/