Mysql Query With From_unixtime

[jhollin1138]

I am trying to write a query but I have run into a snag.

 

I am trying to convert an UNIX timestamp to something MySQL would like. Everything works great if the date is after epoch, but on dates before epoch it doesn't work.

 

For example, I have this query.

>SELECT FROM_UNIXTIME( -13391999 )

This should convert to "1969-07-30 00:00:01" however it converts to NULL.

 

How do I convert UNIX timestamps before epoch inside a query?

[TweezerMan]

From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).

 

If your query is being constructed in PHP, you can use PHP to convert the timestamp, then place that result in your MySQL query:

>$mydate = gmdate('Y-m-d H:i:s', -13391999);

In the above example, $mydate will contain '1969-07-30 00:00:01'.

[jhollin1138]

From what I can tell, MySQL's FROM_UNIXTIME() function does not support negative epoch timestamps (just as the inverse function UNIX_TIMESTAMP() does not either).

 

If your query is being constructed in PHP, you can PHP to convert the timestamp, then place that result in your MySQL query:

>$mydate = gmdate('Y-m-d H:i:s', -13391999);

In the above example, $mydate will contain '1969-07-30 00:00:01'.

That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.

Edited September 29, 2006 by jhollin1138

[telcor]

That is what I was afraid of. I also determined that UNIX_TIMESTAMP didn't support it either. Unfortunately, the query is currently inside MySQL and not coming from PHP. I might have rethink things.

 

According to this MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.

 

Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?

[jhollin1138]

According to this MySQL does not support negative UNIX timestamps (as already noted). From your original question, I take it you are already storing values as a UNIX timestamp? If so then you will have to handle the timestamp to date conversion at the application level (in the application code) rather than in the database.

 

Are you trying to write a query to retrieve data from an already developed data model? Or are you still in the process of defining your data model?

Actually I am migrating my existing phpBB forum to SMF. I had a birthday mod installed on my phpBB forum and wanted to bring this information over too. The phpBB birthday mod was storing data as the UNIX timestamp (well not directly) and SMF stores the data as a MySQL date stamp. I wanted to modify the existing conversion query for phpBB to SMF and discovered the problem with negative timestamps.

 

I already wrote a PHP script that queries the phpBB database and updates the SMF database. I was just hoping to be able to modify the existing conversion script then to have to write a new one, which I did.

[fat_kid]

Hi,

I know this is really old, but I found this forum when trying to figure out the same problem.

In the end the solution was really simple. This is what I did, hopefully it will help others as well:

>SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL unix_timestamp SECOND)

where unix_timestamp is a positive or negative number.

e.g.

>SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -3600*24 SECOND)

will show '1969-12-31 12:00:00', or as in the above examples

>SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -13391999 SECOND)

will show '1969-07-30 12:00:01'.

[TCH-Dick]

Thanks for sharing and welcome to TotalChoice.

[TCH-Thomas]

Welcome to the forum, fat_kid.

[TCH-Bruce]

Welcome to the forums fat_kid

 

Thanks for sharing.