I am very new to this so I apologize if I get confusing with my post.

I have a database that I access through CPanel then through phpMyAdmin. I am running a query to find out when the last login was made by users and I am getting values such as '1233345786' returned.

I need to be able to convert this integer value to a date value of 'MM/dd/yyyy'. When I try the field is returning NULL as the value. When I try Convert_Date the query returns an error saying my table does not contain this function.

And just to clarify further, I can see the correct dates when viewed via my PHP plugin but when I run it via SQL from the database backend that is when I am getting my error. I need to be able to see this from the backend as well because I believe my PHP code for the plugin that I created is flawed.

Can anyone help? I am running out of options.

Why not get the 'user_lastvisit' data and place it in a variable, e.g. $last and then do this: $last_visit = date($last,'d m Y')
Is that too much of a mess?

I could do that if I was trying to get the PHP page to display the date. I already have that working with a Convert_Date function. I am trying to run a SQL Query inside the database itself via myPHPAdmin and get the Integer value converted to date.

Hi.

This integer date thing is a Unix timestamp, I assume?
If so, check out FROM_UNIXTIME

If not, how exactly is it formatted?

P.S.
You should ALWAYS store dates and times in the standard date/time format (YYYY-MM-DD HH:SS), and store them in date type fields, to avoid problems just like this one.

I will try the FROM_UNIXTIME and see if that works. Unfortunately, I did not create the database that I am having to work with. I would have definitely used the standard date/time format if I had created it.

I appreciate all of your help.

This advice is wrong, totally wrong, could not be more wrong

A database is a machine construct designed for the efficient handling of data by a logical machine
Best practice is to store a single 10 digit unix timestamp in the database

there is no "standard date/time format (YYYY-MM-DD HHS)" every user/region has a different date time standard.

the unix timestamp is easily selectable for date ranges, before after or any comparison because it is solely numeric

12/01/08 which could be

12 January 2008
1 December 2008
8 January 2012

depending on user location
a 10byte timestamp is smaller than a 50byte text representation which is important as the database grows (500million date strings)
The data is stored correctly, and should only be formatted on output, to the preference of the viewer depending on local custom.
This is important in database optimization
globalization and
localization

This is such a common topic that can be debated in so many different ways. The fact is BOTH the unix timestamp and mysql date fields are standards and php and mysql both provide the necessary mechanisms for converting between the two.

So I completely DISAGREE with always storing date time information in a textual fashion. Both have their place, both advantages and disadvantages.

^ beat me to the punch. What he said

Sorry had to correct my entry: should have been: date('d m Y', $last)
Using Atli's suggestion, try this:

BTW: Nice one Atli, didn't know about this one - will save a few lines of code in future

the FROM_UNIXTIME worked like a champ!!!!

Thank you all for your help.

Glad it worked, jugg3r :)

@mschroeder and almostbob

I'm not saying you shouldn't use timestamps, but not in an integer field. You can, obviously, but MySQL doesn't consider it a date, which, unconverted, excludes it from much of (if not all of) the built in MySQL date handling functionality, which can cause confusion (as evident by this thread).

And I did NOT, nor will I ever, recommend strong dates as text.
If you think that is what happens when you use the built in date types... you must not think much of the MySQL devs.

Allow me to recite the highlights of 10.3.1. The DATETIME, DATE, and TIMESTAMP Types, from the manual.

MySQL has a special TIMESTAMP data type, which is a 4 byte MySQL specific version of a Unix-like timestamp . If you plan on storing a timestamp, this would be the type to use.
Note that this is not the exact same thing as a Unix timestamp, but it seems to have the same range and is it is comparable, if not smaller, in size.
Also note that if you need a Unix timestamp output, you can still use this type, including all the MySQL features, and convert it when you output it.

The other two, DATE and TIME are both 4 bytes (integer packages), and the DATETIME type is a 8 byte combo of both.
They are NOT 50byte string representation of the data they store.
The advantage with these is that they can store almost an indefinitely high date (year 1000 - 9999), whereas a Unix timestamp, and the MySQL timestamp, can only go as high as 2039-01-09 (and change).

When I say the format "YYYY:MM:DD HH:MM:SS" is the "standard", I am not talking about any sort of localized regional nonsense.
This is the format MySQL prefers, even with the TIMESTAMP type, and this is the format MySQL will always return DATETIME values to you, regardless of whatever localization you use.

This is why I advice using the "standard" format with the built in date type functions and functionality. It makes little sense to not take advantage of that by storing the date data as a number. (Unless you have a compelling reason not to.)

Did I forget something?
I'll come back to it... got to run.