I need to convert the UNIX_TIMESTAMP() output into a readable date and time for output in a table. Anyone know how to do this? or am I in the wrong area again?

thanks, I checked that out and it almost works, perhps you can help me figure out why its not giving all the information? The result it gave me was just the day of the month (21), nothing else.

Here is the code that I put in: date('j-n-Y h:i:s a') which should be displaying the full date and time (02-21-2013 12:21pm), unless my logic is wrong?

Member Avatar for diafol
$ts; // timestamp value

echo date('j-n-Y h:i:s a', $ts);

can you elaborate on that a little D?

here is the full code where I am using the date function

if(isset($_POST['enter']))
{
    $client = stripslashes(htmlspecialchars($_POST['name']));
    $time = date('j-n-Y h:i:s a');
    $sql = "INSERT INTO chatSession(user_name, timestamp) VALUES('".$client."', '".$time."')";

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }else
    {
        if($_POST['name'] != "")
        {  
            $_SESSION['name'] = stripslashes(htmlspecialchars($_POST['name']));
        }else
        {  
            echo '<span class="error">Please type in a name</span>';  
        }
    }
}

do I need to make $ts = UNIX_TIMESTAMP? or what?

The date() function takes two arguments. The first argument specifies the date format you want the timestamp to be converted to. The second argument specifies the timestamp that will be converted. Without the second argument, how does the date() function know what the date is?

everything is there in the same format as it is done on php manual date is j-n-Y time is h:i:s a. both arguments are present, if you are saying i need a comma to seperate them then that makes php manual kinda unreliable as I coppied the argument structure from the manual itself.

Member Avatar for diafol

Sorry GR. I'm totally lost with regard to what you're trying to do. This unix timestamp - are you storing it in the DB? The 'normal' date format - what's happening with this - from your code it looks as though you're storing this. Could you rephrase your needs / use maybe?

yes I am storing the date and time in a table to be displayed as a 'logged in time' for a live chat addon I am working on, yes I know there are all kinds of addons for a live chat, however I am not allowed to use these because my employer is really paranoid about anything you have to download and therefore wants me writing everything from scratch.

basically it is being stored so that I can then display it to the table created on the techs end so that the tech can select the customer that has been waiting the longest for assistance.

Member Avatar for diafol

OK, store datetimes either as unix timestamp (INT) or datetime (DATETIME) - which will be in the Y-m-d H:i:s format. DO NOT store date/times in custom format if you can avoid it. Searching and comparing dates is then very difficult.

You can use a number of different php functions, e.g. date() to format a timestamp for display purposes.

If you want to insert a unix timestamp, you have a couple of options:

$time = time(); // and then insert this

OR

INSERT INTO ... `ts` = UNIX_TIMESTAMP()

ok getting the unix timestamp into the DB is easy i had that before, what about translating it to normal format when it is retrieved from the server so you dont see just millions of seconds displayed? which is the orrigional question I had asked.

The date() function takes two arguments. The first argument specifies the date format you want the timestamp to be converted to. The second argument specifies the timestamp that will be converted. Without the second argument, how does the date() function know what the date is?

The second argument for date() is optional :)

@GR, how about

$sql = "INSERT INTO chatSession(user_name, timestamp) VALUES('".$client."', NOW())";

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now

using NOW() only displays the year, thanks for the input though. I have no idea why these commands are not executing propperly.

Member Avatar for diafol

using NOW() only displays the year, thanks for the input though. I have no idea why these commands are not executing propperly.

You may have set your INT size in your Table FIELD to too small a number for the timestamp field. Leave the size blank for now.

ok getting the unix timestamp into the DB is easy i had that before, what about translating it to normal format when it is retrieved from the server so you dont see just millions of seconds displayed? which is the orrigional question I had asked.

Yes bu t you then showed the code which totally tries to do the opposite of what you're asking.

You extract the timestamp from the DB:

$ts = $row['timestamp'];

Or whatever you're using, then

echo date('j-n-Y h:i:s a', $ts);

As I originally posted.

Thats why I asked for you to elaborate on that D, I even asked if it was suposed to be the UNIX_TIMESTAMP. Thank you for your help I will work on it more.

that worked out beautifully, Thank you D. I just had to switch the positions of 'j' and 'n' for it to read out properly.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.