# Query using column generated at table creation

**URL:** <https://community.cratedb.com/t/query-using-column-generated-at-table-creation/980>\
**Category:** CrateDB\
**Created:** [January 12, 2022, 7:28am UTC](https://community.cratedb.com/t/query-using-column-generated-at-table-creation/980 "2022-01-12T07:28:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![john](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@john](https://community.cratedb.com/u/john)\
**Post date:** [January 12, 2022, 7:28am UTC](https://community.cratedb.com/t/query-using-column-generated-at-table-creation/980/1 "2022-01-12T07:28:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [January 12, 2022, 7:41am UTC](https://community.cratedb.com/t/query-using-column-generated-at-table-creation/980/2 "2022-01-12T07:41:03Z")

</div>

Hi @john,

did you already try [`DATE_FORMAT`](https://crate.io/docs/crate/reference/en/master/general/builtins/scalar-functions.html#date-format-format-string-timezone-timestamp)?

```sql
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`)

---

<div class="post-metadata">

**Author:** ![john](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@john](https://community.cratedb.com/u/john)\
**Post date:** [January 12, 2022, 8:25am UTC](https://community.cratedb.com/t/query-using-column-generated-at-table-creation/980/3 "2022-01-12T08:25:00Z")

</div>

Many thanks @hammerhead. Perfect.