timestamp

MySQL has some nice functions also:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

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

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.

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

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

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

Thank you very much.