How do I convert UNIX timestamps to the date format d.m.Y inside a query

I am trying to write a query but I have run into a snag. I am trying to convert an UNIX timestamp to something PHP would like.

For example, I have this query.
SELECT Name, FROM_UNIXTIME(UNIX_TIMESTAMP(Date_of_birth),'%d.%m.%Y') As Date_of_Birth FROM Test
This should convert to "11.08.1964" however it converts to 01.01.1970

How do I convert UNIX timestamps to the date format d.m.Y inside a query?

I'm attaching my sample files and database.

Regards
Rune

Try using the Dateadd call instead and see if that works for you. Here's a reference so you can take a look at what this function does:
ms186819.aspx

Firstly the value you have in your db (INSERT INTO `Test` VALUES ('1', 'Rune Brynestad', '-170125200')) is not a unix timestamp

When you have corrected that the correct way to convert this would be:

SELECT Name, FROM_UNIXTIME(Date_of_birth,'%d.%m.%Y') As DOB FROM Test

Tip: Always give your alias a unique name

Have done some more testing. From what I see, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).

It looks like I could use the DATE_ADD function

SELECT Name, DATE_ADD('1970-01-01', INTERVAL Date_of_birth second) As DOB FROM Test

gives me 1964-08-11 00:00:00 which is correct.

What I need now is a way to format this date the way we use it in Norway which is 11.08.1964 or dd.mm.yyyy

It still have to be done inside the query. Could it be done ?

Regards
Rune

Try this:

SELECT Name, DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL Date_of_birth SECOND), '%d.%m.%Y') As DOB FROM Test

It works like a dream.

Thanks a lot Dave.

Regards
Rune

No worries, it was an interesting question and I learnt something in the process of solving it so we both gained something.