I have a column called tidpunkt in the database that stores the dates in timestamp formats. How do I extract only the year with SQL? I still want the extracted year to be in timestamp.
I've tried like this:
SELECT EXTRACT(YEAR FROM tidpunkt) as tidpunkt FROM ringupp
But it does not work. Am I doing something wrong?
SELECT YEAR(tidpunkt) as tidpunkt FROM ringupp
Do this
SELECT FROM_UNIXTIME(tidpunkt , '%Y') AS tidpunkt FROM ringupp
MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp. The return value is in ‘YYYYY-MM-DD HH:MM:SS’ .
See DOCUMENTATION . Also see this Example
Try this:
if you require 4 digit year
SELECT DATEPART('yyyy',tidpunkt) as tidpunkt FROM ringupp
and if 2 digit year is required:
SELECT DATEPART('yy',tidpunkt) as tidpunkt FROM ringupp
Documentation here
date_format is your friend with mysql
see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
SELECT DATE_FORMAT( tidpunkt, '%Y' ) as tidpunkt FROM ringupp
SELECT EXTRACT(YEAR FROM tidpunkt) FROM ringupp
