Solved

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

Posted on 2008-10-03
9
3,706 Views
Last Modified: 2012-05-05
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
Comment
Question by:kenuk110
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22639788
SELECT FROM_UNIXTIME(yourDateFieldName);
0
 

Author Comment

by:kenuk110
ID: 22639799
Hi,

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

Regards

Ken
0
 
LVL 82

Expert Comment

by:hielo
ID: 22639808
SELECT FROM_UNIXTIME(yourDateFieldName) as theDate FROM yourTable;
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:kenuk110
ID: 22639817
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
 

Author Comment

by:kenuk110
ID: 22639819
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
 

Author Comment

by:kenuk110
ID: 22639842
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
 

Author Comment

by:kenuk110
ID: 22640385
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
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 22641150
>>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
 

Author Closing Comment

by:kenuk110
ID: 31502987
Brilliant, thank you very much for this, worked perfectly.

Great response,

Cheers

Ken
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question