Converting int(10) to Date Format

[jugg3r]

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

SELECT date_format(`user_lastvisit`, '%d %m %Y')

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.

[diafol]

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?

[jugg3r]

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.

[Atli]

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.

[jugg3r]

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.

[almostbob]

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

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[INDENT]there is no "standard date/time format (YYYY-MM-DD HH:SS)" every user/region has a different date time standard.[/INDENT]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[INDENT]12 January 2008
1 December 2008
8 January 2012[/INDENT]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

[mschroeder]

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.

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

[diafol]

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.

Sorry had to correct my entry: should have been: date('d m Y', $last)

Using Atli's suggestion, try this:

SELECT FROM_UNIXTIME(user_lastvisit,'%d %m %Y');

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

[jugg3r]

the FROM_UNIXTIME worked like a champ!!!!

Thank you all for your help.

[Atli]

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.

[diafol]

I'm hugely confused Ted. Should mysql date fields be datetime format or int?? I always assumed they should be datetime (yyyy-mm-dd hh:mm:ss). Is there an advantage to storing the datetime as an unix timestamp or mysql timestamp? Having said that, before the I was aware of the aforementioned function (FROM_UNIXTIME), I had to parse/format the timestamp.

[Atli]

I'm hugely confused Ted. Should mysql date fields be datetime format or int?? I always assumed they should be datetime (yyyy-mm-dd hh:mm:ss). Is there an advantage to storing the datetime as an unix timestamp or mysql timestamp? Having said that, before the I was aware of the aforementioned function (FROM_UNIXTIME), I had to parse/format the timestamp.

The MySQL date types: DATE, TIME, DATETIME and TIMESTAMP, all expect input in the "standard" string format , and they all return them to you in the same format.(It does accept a variety of alternative formats as input tho. See the manual entry I posted earlier.)

The data is obviously not stored in this string format, but it is represented that way when used.

Putting a Unix timestamp into a INT field leaves the handling of that date completely to you. MySQL is no aware that this is a date, and will consequently not be able to use it with any of the built in date functions and other functionality.
(As far as I can tell by glancing over the date function list , anyways.)

So the short answer: use the date types and the standard string format, unless you want/need to handle the dates completely by your lonesome :)

P.S.
Who's Ted?

[diafol]

Thanks Atli - great response.

Ted - oh it's an often-used quote from an old sitcom from Ireland called 'Father Ted' - by an incompetent priest that always got the wrong end of the stick. Sorry I was being a bit provincial there!