converting now() ?

Let's say I have an simple SQL query:

INSERT INTO news (id,subject,date_time) VALUES ('','$subject',now());

the now() function (not sure if it is a function, I found no documentation of it on php.net) is giving me this:

2002-07-05 18:03:28

But I want to convert this date to an european date/time like this:

05-07-2002 18:03:28

How can I convert this? is there a function for this orso?
Or maybe I must insert the date/time like this:

$date_time = date("d-m-Y H:i:s");

into my database?
But if I do it that way, I can't order my SQL query by the time/date en with now() I can ORDER BY date_time.

That's my point, I want to ORDER BY date_time, but on the page itself my visitors must read a ''good'' european time.

Any ideas for this?

Robbie.

Youa re gonna have to store it like that, but you can format it once you retrieve it. Then you can convert it into a unix timestamp by a mysql query as shown below


UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time:
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will receive the value directly, with no implicit ``string-to-unix-timestamp'' conversion. If you give UNIX_TIMESTAMP() a wrong or out-of-range date, it will return 0.


Then use the date() (http://www.php.net/date) function to format the nix timestamp like this


$result = mysql_query("select UNIX_TIMESTAMP('1997-10-04 22:23:00')");
list($timestamp) = mysql_fetch_array($result);
$date_time = date("d-m-Y H:i:s",$timestamp);


or format it through a query as you select it like below

DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string: %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'



Hope this helps

Jee :)

U 0wn jee...

thanks alot!

No Problems mate :) Hope you had fun making it work :)

Jee