Solved

timestamp

Posted on 2011-03-01
8
623 Views
Last Modified: 2012-08-13
Is there a way of getting the timestamp displayed in time form from a mysql query rather than having to convert it within php?
0
Comment
Question by:PeterErhard
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35005318
0
 
LVL 14

Expert Comment

by:Kalpan
ID: 35005417
you use FROM_UNIXTIME as below example


> SELECT create FROM phpkit ;

The output
----------------
Created
--------------
1216053466
-------------------
> SELECT DATE( FROM_UNIXTIME( `created` ) ) AS pDate FROM phpkit

OUTPUT as follows,
------------------------------
2008-07-14
------------------------------
0
 

Author Comment

by:PeterErhard
ID: 35005436
Can you please point me towards the function I'm after? I've had a look at the description of each one, and can't see which one I'm after.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:PeterErhard
ID: 35005478
Thanks kalmax, that gave me the date, but I need the time as well. How can I do that?
0
 
LVL 7

Expert Comment

by:Vimal DM
ID: 35005514
Hi,

See the following points,you can get some ideas on this

1) Compute the current date.

    SELECT date('now');

2) Compute the last day of the current month.

    SELECT date('now','start of month','+1 month','-1 day');

3) Compute the date and time given a unix timestamp 1092941466.

    SELECT datetime(1092941466, 'unixepoch');

4) Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

    SELECT datetime(1092941466, 'unixepoch', 'localtime');

5) Compute the current unix timestamp.

    SELECT strftime('%s','now');

6) Compute the number of days since the signing of the US Declaration of Independence.

    SELECT julianday('now') - julianday('1776-07-04');

7) Compute the number of seconds since a particular moment in 2004:

    SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

8) Compute the date of the first Tuesday in October for the current year.

    SELECT date('now','start of year','+9 months','weekday 2');

9) Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

    SELECT (julianday('now') - 2440587.5)*86400.0;


I have taken this from a google searched pages
0
 

Author Comment

by:PeterErhard
ID: 35005581
and they all error out with similar errors to the following:

Error
SQL query:

SELECT DATETIME( 1298403320,  'unixepoch',  'localtime' )

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1298403320, 'unixepoch', 'localtime')' at line 1
0
 
LVL 14

Accepted Solution

by:
Kalpan earned 500 total points
ID: 35005796
please try as below

SELECT CONCAT(DATE(FROM_UNIXTIME(1216053466))," ",TIME(FROM_UNIXTIME(1216053466))) AS pDate;
0
 

Author Closing Comment

by:PeterErhard
ID: 35005893
Thank you very much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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