mySQL - date/time formatting

[chrisjl]

I'm dabbling with the database behind a phpBB forum (to extract viewing stats), but my minimal SQL knowledge is letting me down, and Google is returning too many matches to distinguish the useful ones!

Anyway,...

mysql> select topic_time from phpbb_topics;

...returns a list of seconds since 1970, but I want formatted times.

I tried:-

mysql> select DATE_FORMAT(topic_time, '%H:%i:%s') from phpbb_topics;

But that just returns a load of 'NULLS'. Can it be done in one step?

[judas]

Try using TIME_FORMAT instead of DATE_FORMAT

[chrisjl]

My example used h:m:s, but I do actually want to return a date. It seems DATE_FORMAT doesn't like the values given (e.g. earlier today was 1090512328)

[anonymous-user]

Not sure if phpBB uses MySQL timestamps or Unix timestamps generated within the php code. Either way, this page and mainly the comments at the bottom should give you a clue or two:
www.weberdev.com/get_example.php3/26

[chrisjl]

Another detail I left out of the original was that this is a cron'd perl script, so PHP functions aren't relevant.

Anyway, the (simplified for clarity) solution:-

select DATE_FORMAT( FROM_UNIXTIME(user_lastvisit), '%e/%m/%Y' ),username from phpbb_users;

I was missing the conversion from UNIXTIME to mySQL time.