• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3732
  • Last Modified:

Convert UNIX timestamp to MySQL date format YYYY-MM-DD 00:00:00?

Hi,

I have a piece of software which takes SMS's from a mobile phone and puts that message straight in to a MySQL database table. The software creates the timestamp of the SMS in UNIX format, 10 digits. I need this information to be presented in MySQL format: YYYY-MM-DD 00:00:00.

I have looked at many sites stating how this can be done but as yet I'm unable to get it working.

My table is called SMSIN and the fields I need are timestmp , message  and number.

Even if I have to run a script that copies these values in to another table for me to use that would be fine, if not can another field in the same table have the real date in it?

Any help would be much appreciated.

Regards

Ken
0
kenuk110
Asked:
kenuk110
  • 6
  • 3
1 Solution
 
hieloCommented:
SELECT FROM_UNIXTIME(yourDateFieldName);
0
 
kenuk110Author Commented:
Hi,

Thanks for this, how do I tell it which table to use?

Regards

Ken
0
 
hieloCommented:
SELECT FROM_UNIXTIME(yourDateFieldName) as theDate FROM yourTable;
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
kenuk110Author Commented:
That works great, I can see the values now appearing on screen.

The next part of the question is how would I get this to either insert these in to the same table under a new field - mysqltimestmp or even copy the results to a new table.

I have seen a SELECT INTO function but don't know the correct syntax to use?


0
 
kenuk110Author Commented:
Sorry, one more thing, if the output is going into a new table, I also need the field 'message' and 'sender' fields to be present.

Is this possible?

If I can output the value in to the same table that would be better, not sure if that is possible too?
0
 
kenuk110Author Commented:
Hi again,

I got this to work but still only to the screen:

SELECT FROM_UNIXTIME(TIMESTMP) AS theDate, message FROM SMSIN

message is the sms message I get.

Still not sure how to get this in to another field though.
0
 
kenuk110Author Commented:
Hi,

Sorry for this request but it's pretty urgent that I find a solution to this, ANY help with my last post would be much appreciated.

Cheers

Ken
0
 
hieloCommented:
>>Still not sure how to get this in to another field though.
assuming you have another field on the same table named goodDate:

UPDATE SMSIN SET goodDate=FROM_UNIXTIME(TIMESTMP)

that will update goodDate with the date format you seek
0
 
kenuk110Author Commented:
Brilliant, thank you very much for this, worked perfectly.

Great response,

Cheers

Ken
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now