Posted on 2011-03-01
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?
Question by:PeterErhard
LVL 142

Expert Comment

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

Expert Comment

ID: 35005417
you use FROM_UNIXTIME as below example

> SELECT create FROM phpkit ;

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

OUTPUT as follows,

Author Comment

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.
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


Author Comment

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

Expert Comment

by:Vimal DM
ID: 35005514

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

Author Comment

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

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
LVL 14

Accepted Solution

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


Author Closing Comment

ID: 35005893
Thank you very much.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Wordpress Plugin - Search From Action to Another Plugin file 13 47
php checkbox validation 5 27
Presenting past 6 months bug 7 25
MySQL Memory Keeps Increasing 4 34
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now