Page 1 of 1

Time Stamp Null Verification

Posted: Tue Jan 11, 2005 12:14 pm
by abshaw
faced with 2 problems

1. have a field "record_date" in mysql as type "timestamp". the value saved is "20050111011408" . Now i want to format this date as the date of record creation.

Code: Select all

print date("l dS of F Y h:i:s A",$rowї'record_date'])
is the line i am using to convert/ format/ interpret this value which comes out to be
"Monday 18th of January 2038 07:14:07 PM" where as it should be
"Monday 11th of January 2005...." what could be the problem.


2. the second part of the problem is that i want to check if there is any null value for this field, logically it should be

Code: Select all

if(is_null($rowї'record_date'])) 
{print ("unknown");} 
else {print ("known");}
suggestions?

Posted: Tue Jan 11, 2005 1:51 pm
by feyd
mysql type timestamp does not correlate to unix timestamps (what date() wants)

You need to convert the number to unix timestamp.. this can be done via UNIX_TIMESTAMP().. you may want to look at EXTRACT(), or DATE_FORMAT() as well.

MySQL timestamps are mostly used to keep records of when something was updated or inserted, automatically. I'd suggest using a DATETIME, DATE, or INT(10) UNSIGNED type if that was not your intention with the type.

http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html

Posted: Tue Jan 11, 2005 4:52 pm
by abshaw
thanks man , the link that you provided works perfectly, my host server is in california and i am central time so the line of code that is now actually working for me is

Code: Select all

Select DATE_FORMAT((record_date + INTERVAL 2 HOUR),'%W, %M %D, %Y at %T') AS ttime from tbl_test
once this line is put in as is the out put comes out as
Tuesday, January 11th, 2005 at 15:01:45
thanks once again.

Posted: Tue Jan 11, 2005 4:56 pm
by abshaw
by the way forgot to add one thing in there, my second problem that i was facing "null" issue, is still out there. Although if the value is null, the above mentioned code line takes care of most of the fields by not putting any thing in place, but if some body wants to replace with a customized text then its a problem (for me at least).
:D

Posted: Tue Jan 11, 2005 5:27 pm
by feyd
for switching code paths based on null check out ISNULL() function: http://dev.mysql.com/doc/mysql/en/Compa ... ators.html
and the IF() function:
http://dev.mysql.com/doc/mysql/en/Contr ... tions.html