1,105,290 Community Members

converting UNIX_TIMESTAMP() to be readable output

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
deceptikon
Eternally Awesome
4,663 posts since Jan 2012
Reputation Points: 1,339 [?]
Q&As Helped to Solve: 679 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 
Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
1
 
$ts; // timestamp value

echo date('j-n-Y h:i:s a', $ts);
Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

can you elaborate on that a little D?

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
deceptikon
Eternally Awesome
4,663 posts since Jan 2012
Reputation Points: 1,339 [?]
Q&As Helped to Solve: 679 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
0
 

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?

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

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?

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

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()
Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
EvolutionFallen
Posting Pro
504 posts since Aug 2009
Reputation Points: 107 [?]
Q&As Helped to Solve: 108 [?]
Skill Endorsements: 8 [?]
 
0
 

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

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
GraficRegret
Junior Poster
182 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 1 Year Ago by diafol, deceptikon and EvolutionFallen
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article