Jump to content
jhollin1138

Mysql Query With From_unixtime

Recommended Posts

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? :)

Share this post


Link to post
Share on other sites

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'.

Share this post


Link to post
Share on other sites
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 by jhollin1138

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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'.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×