Is there a way of getting the timestamp displayed in time form from a mysql query rather than having to convert it within php?
Who is Participating?
KalpanConnect With a Mentor Commented:
please try as below

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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,
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

PeterErhardAuthor Commented:
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.
PeterErhardAuthor Commented:
Thanks kalmax, that gave me the date, but I need the time as well. How can I do that?
Vimal DMSenior Software EngineerCommented:

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
PeterErhardAuthor Commented:
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
PeterErhardAuthor Commented:
Thank you very much.
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.

All Courses

From novice to tech pro — start learning today.