Page 1 of 1
Timestamp Conversion
Posted: Fri Apr 07, 2006 5:33 pm
by AliasBDI
I have always had problems with converting
timestamp and
datetime. I echo (from a MySQL database) the timestamp field and it give me this: 1143930624. How do I know whether this is a UNIX TIMESTAMP or not? And then how do I convert it?

Posted: Fri Apr 07, 2006 5:42 pm
by feyd
AliasBDI wrote:1143930624
is a unix timestamp. Why? it's too large for a "standard" textual one (often in the format YYYYMMDD or YYYY-MM-DD)
Now, how does one convert it? Convert it to what? To MySQL standard notation for a DATE field,
date('Y-m-d', 1143930624); for a DATETIME field
date('Y-m-d H:i:s', 1143930624); for a TIMESTAMP
date('YmdHis', 1143930624); for a TIME field
date('H:i:s', 1143930624), etc etc.
Posted: Fri Apr 07, 2006 6:04 pm
by AliasBDI
I'm wanting to echo it out to 'MM-DD-YYYY'. I tried this date('m-d-Y', $row_conDETAILS['createDATE']); but it does not work. It gives me '01-18-2038'. It should give '04-01-2006'.
*It looks like when I originally posted my full echo of the timestamp did not post. Nevertheless, according to your standards, it is still a timestamp. This is what it echos with no MySQL query conversion: '20060401163024'. Does it matter that this is a Windows Server?
Posted: Fri Apr 07, 2006 6:23 pm
by feyd
20060401163024 is not a unix timestamp. It is a standard MySQL TIMESTAMP field. I'd suggest using DATE_FORMAT() found in MySQL to make it the format you wish.
Posted: Fri Apr 07, 2006 6:31 pm
by AliasBDI
Great, works. I put this in my SELECT query: 'DATE_FORMAT(createDATE,'%m-%d-%Y') AS createDATE'. Would this be the same concept for a datetime field instead of a timestamp?
Posted: Fri Apr 07, 2006 6:59 pm
by feyd
yes, it the same sort of thing.