convert time format in mysql query

Question

I have a database table in (Drupal) which time is stored in a format like 1328105887 . I want to write a where condition w.r.t. this date where is year is 2014 and output date will be in (d-m-y) format.

My table sturcture is like this

SELECT `sid`,`nid`,`uid`,`is_draft`,`submitted`,`remote_addr` FROM `my_table`

Help me to achieve this only with mysql query .

possible duplicate of [Convert timestamp to date in MySQL query](http://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query) — Jim Wright, Jun 04 '15 at 07:15
Which is this format ? Can you tell the equivalent date for **1328105887**? — Akhil, Jun 04 '15 at 07:18

score 1 · Accepted Answer · answered Jun 04 '15 at 07:19

1

SELECT  from_unixtime(1328105887) from tableName
returns : 2012-02-01 19:18:07

Now

 SELECT DATE_FORMAT(from_unixtime(1328105887) ,'%m-%d-%Y') 
 returns: 02-01-2012

if you need just year use this:

 SELECT DATE_FORMAT(from_unixtime(1328105887) ,'%Y') 
 returns: 2012

use it in where clause as:

where DATE_FORMAT(from_unixtime(1328105887) ,'%Y')='2014'

so the final query would be

  SELECT `sid`,`nid`,`uid`,`is_draft`,`submitted`,`remote_addr` FROM 
  `my_table` 
  where DATE_FORMAT(FROM_UNIXTIME(`date_column`),'%Y') = '2014'

answered Jun 04 '15 at 07:19

Danyal Sandeelo

12,196
10
47
78

yeah i tried but what about the where condition I need result of year 2014 only – Vipul sharma Jun 04 '15 at 07:25
@vipulsharma I have added that..check my answer .. i wrote the complete query in the end – Danyal Sandeelo Jun 04 '15 at 07:25

score 0 · Answer 2 · answered Jun 04 '15 at 07:29

0

Working

SELECT DATE_FORMAT(FROM_UNIXTIME(submitted), '%d-%m-%Y') AS 'date_formatted' FROM ma_webform_submissions where year(FROM_UNIXTIME(submitted)) = '2014'

answered Jun 04 '15 at 07:29

Vipul sharma

1,245
1
13
33

it is okay,, since you marked my answer as answered. you don't need to write it again in answer :) .. – Danyal Sandeelo Jun 04 '15 at 07:33

convert time format in mysql query

2 Answers2