Query using column generated at table creation

CrateDB
1

Hi.
I used the following to create a table:
create table .....( ..., discovery_date_d STRING, year timestamp with time zone GENERATED ALWAYS AS date_trunc('year', discovery_date_d)) PARTITIONED BY (year);

I now wish to query as follows:

select year, sum(...) from ... group by year

How do I get the ‘year’ to display in a readable format like ‘1970’ and not ‘1420070400000’

Sorry for the noob question.

2

Hi @john,

did you already try DATE_FORMAT?

SELECT DATE_FORMAT('%Y', DATE_TRUNC('year', NOW()))

Returns: 2022 (as text, if you need it to be numeric, you can also cast it to integer)

3

Many thanks @hammerhead. Perfect.