Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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