PHPBuilder - FormatMySQLDateTime



RSS Twitter
Snippets Calendars Dates

FormatMySQLDateTime

by: Truman Leung
|
February 2, 2003

Version: 1

Type: Function

Category: Calendars/Dates

License: GNU General Public License

Description: Takes date, time, datetime, timestamp and year MySQL values and converts them in to nice date/time formats.



/**
	str FormatMySQLDateTime (mysql time, mysql datetime column type, string format)

	Receives a mysql datetime format: date, time, datetime, timestamp, year
	returns formated date/time string based on PHP symbols 

	mysql date format: YYYY-MM-DD
	mysql time format: either HH:MM or HH:MM:SS
	mysql datetime format: YYYY-MM-DD HH:MM:SS
	mysql timestamp format: YYYYMMDDHHMMSS
	mysql year format: YYYY

	l (lowercase 'L') - day of the week, textual, long; e.g. "Friday" 
	D - day of the week, textual, 3 letters; e.g. "Fri" 
	F - month, textual, long; e.g. "January" 
	M - month, textual, 3 letters; e.g. "Jan" 
	n - month without leading zeros; i.e. "1" to "12" 
	m - month; i.e. "01" to "12" 
	j - day of the month without leading zeros; i.e. "1" to "31" 
	d - day of the month, 2 digits with leading zeros; i.e. "01" to "31" 
	S - English ordinal suffix for the day of the month, 2 characters; i.e. "st", "nd", "rd" or "th" 
	Y - year, 4 digits; e.g. "1999" 
	y - year, 2 digits; e.g. "99" 
	z - day of the year; i.e. "0" to "365" 

	a - "am" or "pm" 
	A - "AM" or "PM" 
	g - hour, 12-hour format without leading zeros; i.e. "1" to "12" 
	G - hour, 24-hour format without leading zeros; i.e. "0" to "23" 
	h - hour, 12-hour format; i.e. "01" to "12" 
	H - hour, 24-hour format; i.e. "00" to "23" 
	i - minutes; i.e. "00" to "59" 
	s - seconds; i.e. "00" to "59" 

	$asc_datetime_format['long date'] = 'l, F j, Y'; 
	$asc_datetime_format['normal date'] = 'F j, Y'; 
	$asc_datetime_format['medium date'] = 'M j, Y'; 
	$asc_datetime_format['short date'] = 'M-d-y'; 
	$asc_datetime_format['numeric date'] = 'n/j/y'; 
	$asc_datetime_format['normal time'] = 'g:i a'; 
	$asc_datetime_format['time with seconds'] = 'g:i:s a'; 
	$asc_datetime_format['datetime'] = 'F j, Y g:i a'; 

*/
function FormatMySQLDateTime($mysql_datetime_str, $mysql_datetime_type='date', $php_str_format='F j, Y') {
	if (!empty($mysql_datetime_str)) {

		// DEFAULTS
		$hour		= 0;
		$minute	= 0;
		$second	= 0;
		$month		= 1;
		$day			= 1;
		$year		= 2003;

		$mysql_datetime_type = strtolower($mysql_datetime_type);

		switch ($mysql_datetime_type) {
			case 'date':
				$date_components = explode('-', $mysql_datetime_str);
				if (count($date_components) == 3) {
					list($year, $month, $day) = $date_components;
				} else {
					return false;
				}
				break;
			case 'time':
				$time_components = explode(':', $mysql_datetime_str);
				if (count($time_components) == 3) {
					list($hour, $minute, $second) = $time_components;
				} elseif  (count($time_components) == 2){
					list($hour, $minute) = $time_components;
				} else {
					return false;
				}				
				break;
			case 'datetime':
				$datetime_components = explode(' ', $mysql_datetime_str);
				if (count($datetime_components) == 2) {
					list($date_str, $time_str) = $datetime_components;
					$date_components = explode('-', $date_str);
					if (count($date_components) == 3) {
						list($year, $month, $day) = $date_components;
					} else {
						return false;
					}
					$time_components = explode(':', $time_str);
					if (count($time_components) == 3) {
						list($hour, $minute, $second) = $time_components;
					} elseif  (count($time_components) == 2){
						list($hour, $minute) = $time_components;
					} else {
						return false;
					}				
				} else {
					return false;
				}				
				break;
			case 'timestamp':
				if (strlen($mysql_datetime_str) == 12) {
					$year = substr($mysql_datetime_str, 0, 4);
					$month = substr($mysql_datetime_str, 4, 2); 
					$day = substr($mysql_datetime_str, 6, 2);
					$hour = substr($mysql_datetime_str, 8, 2);
					$minute = substr($mysql_datetime_str, 10, 2);
					$second = substr($mysql_datetime_str, 12, 2);			
				} else {
					return false;
				}
				break;
			case 'year':
				if (strlen($mysql_datetime_str) == 4) {
					$year = $mysql_datetime_str;
					if (!in_array($php_str_format, array('Y','y'))) {
						$php_str_format = 'Y';
					}
				} else {
					return false;
				}
				break;
			default:
				return false;				
		}
		return date($php_str_format, mktime($hour, $minute, $second,$month,$day,$year));
	} else {
		return false;
	}
} // end func

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Truman Leung

Comment:



Comment:

(Maximum characters: 1200). You have characters left.