Evan Traverso February 2016

Extracting year from a date timestamp in SQL

I am trying to convert a column in a database into a timestamp format, then I want to see the number of counts for each year.

I have:

SELECT
    (column_name::date), count(*)
    EXTRACT(YEAR FROM column_name)
FROM table
GROUP BY column_name;

'I get an error message that says no function matches the given name and argument types. You might need to add explicit type casts.'

Is extract the wrong function?

Answers


Juan Carlos Oropeza February 2016

You probably want something like this if column_name is timestamp

search for EXTRACT documentation

SELECT
    EXTRACT(YEAR FROM column_name), count(*)        
FROM table
GROUP BY EXTRACT(YEAR FROM column_name);

if column_name is string you need to_timestamp() function

Post Status

Asked in February 2016
Viewed 1,446 times
Voted 8
Answered 1 times

Search




Leave an answer