u can use highly customizabe MySQL DATE_FORMAT() function for retrieving
date in desired format. Relevant portion from manual is reprodcued below
inside quotes.
For manipulating date in php, u can use the MySQL UNIX_TIMESTAMP(date)
function to return unix timestamp of the date and use the php date functions
like date(), getdate() etc. on it.
What do you mean by php date? The manual says that PHP supports eight
primitive types i.e.:
Four scalar types: boolean, integer, floating-point number (float), string
Two compound types: array, object
And finally two special types: resource, NULL
-------------------quote
DATE_FORMAT(date,format)
Formats the date value according to the format string. The following
specifiers may be used in the format string:
Specifier Description
%M Month name (January..December)
%W Weekday name (Sunday..Saturday)
%D Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%Y Year, numeric, 4 digits
%y Year, numeric, 2 digits
%X Year for the week where Sunday is the first day of the week, numeric, 4
digits, used with '%V'
%x Year for the week, where Monday is the first day of the week, numeric, 4
digits, used with '%v'
%a Abbreviated weekday name (Sun..Sat)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%m Month, numeric (01..12)
%c Month, numeric (1..12)
%b Abbreviated month name (Jan..Dec)
%j Day of year (001..366)
%H Hour (00..23)
%k Hour (0..23)
%h Hour (01..12)
%I Hour (01..12)
%l Hour (1..12)
%i Minutes, numeric (00..59)
%r Time, 12-hour (hh:mm:ss [AP]M)
%T Time, 24-hour (hh:mm:ss)
%S Seconds (00..59)
%s Seconds (00..59)
%p AM or PM
%w Day of the week (0=Sunday..6=Saturday)
%U Week (0..53), where Sunday is the first day of the week
%u Week (0..53), where Monday is the first day of the week
%V Week (1..53), where Sunday is the first day of the week. Used with '%X'
%v Week (1..53), where Monday is the first day of the week. Used with '%x'
%% A literal %.
All other characters are just copied to the result without interpretation:
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> select DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
As of MySQL Version 3.23, the % character is required before format
specifier characters. In earlier versions of MySQL, % was optional.
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function above, but the format string
may contain only those format specifiers that handle hours, minutes, and
seconds. Other specifiers produce a NULL value or 0.
-------------------------------quote
regards,
-----Original Message-----
From: Bruce Feist [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 29, 2003 08:17
To: MySQL List
Subject: MySql, PHP, and Dates
I'm experienced with SQL, but new to MySql and PHP, which I'm using
together to create a dynamic web site. Right now, I'm having minor
difficulties with date formatting. It seems that when I retrieve a date
from MySQL into PHP, it shows up as a string instead of a date data
type, with format YYYY-MM-DD HH:MM:SS. Is there any way that I can get
it returned as a PHP date instead? Also, exactly what is it that
controls the output format of the date as a string? Is PHP somehow
requesting that MySql return the date as a string, or is it getting a
date from MySql and then converting it to a string on the PHP side?
What's the best way of controlling date format? Obviously, I could use
string operators to extract the year, month, and day and do whatever I
want to with them, but that's sensitive to how PHP and/or MySql are
configured, I assume; it would be better for me to request the date in a
specific form (ideally one taken from operating system preferences).
Thanks!
Bruce Feist
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
