[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

timestamp

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
PeterErhard
Asked:
PeterErhard
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
KalpanCommented:
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
 
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
PeterErhardAuthor Commented:
Thanks kalmax, that gave me the date, but I need the time as well. How can I do that?
0
 
Vimal DMCommented:
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
 
PeterErhardAuthor Commented:
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
 
KalpanCommented:
please try as below

SELECT CONCAT(DATE(FROM_UNIXTIME(1216053466))," ",TIME(FROM_UNIXTIME(1216053466))) AS pDate;
0
 
PeterErhardAuthor Commented:
Thank you very much.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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