# Timestamp without time zone in local time

**URL:** <https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765>\
**Category:** SQL\
**Created:** [August 3, 2021, 2:39pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765 "2021-08-03T14:39:25Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![inviridi](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/inviridi/32/273_2.png) [@inviridi](https://community.cratedb.com/u/inviridi)\
**Post date:** [August 3, 2021, 2:39pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765/1 "2021-08-03T14:39:25Z")

</div>

Howdy folks,

I am logging machine data and would like to have a time stamp with local time from the machine. The (generated) column is created as

```auto
ts_crate TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS CURRENT_TIMESTAMP

```

and when I query the last entries with

```auto
SELECT * from mibrecords WHERE ts_crate >= CURRENT_TIMESTAMP - INTERVAL '1' MINUTE;

```

I get the proper results although the timestamp is in UTC not GMT+1.

What is the best practice to get the right timestamps? Re-converting with every query seems clunky and offsets are sure to be difficult with summer / winter time.

Thanks a lot for your insights,  
Frank

#### 

edit: If possible, I would like to avoid time zones for simplicity… but Iif thats the way forward, then so be it.

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [August 3, 2021, 3:48pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765/2 "2021-08-03T15:48:06Z")

</div>

Hi @inviridi,

is my interpretation correct that your goal is that all queried timestamps should be automatically converted to a specific timezone?

---

<div class="post-metadata">

**Author:** ![inviridi](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/inviridi/32/273_2.png) [@inviridi](https://community.cratedb.com/u/inviridi)\
**Post date:** [August 3, 2021, 9:27pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765/3 "2021-08-03T21:27:09Z")

</div>

Hi Jayeff,

yes, you are correct.

I am able to convert the timestamps after querying and I know how to add an offset, but I am susceptible to overengineering and would just like know how you would approach this. If I could just query

```auto
SELECT * FROM table 

```

and the proper timestamps would be shown, that’d be awesome. Then again, I am not a SQL guy and if UTC is what everybody is using, then I would go with this.

---

<div class="post-metadata">

**Author:** ![jayeff](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jayeff/32/271_2.png) [@jayeff](https://community.cratedb.com/u/jayeff)\
**Post date:** [August 4, 2021, 9:38am UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765/4 "2021-08-04T09:38:15Z")

</div>

Hi @inviridi,

in general it’s useful to stick to UTC. That’s why CrateDB (but also other databases e.g. PostgreSQL) will always store timestamps in UTC. Converting to a specific time zone is typically more a client concern (which can be you connecting to the database via command line interface).

PostgreSQL allows to to set a timezone for the current session (`SET timezone TO 'Europe/Vienna';`) and all timestamptz will be returned in the defined timezone. This features isn’t currently supported by CrateDB though 😔

For CrateDB you can use either [`timezone()`](https://crate.io/docs/crate/reference/en/4.6/general/builtins/scalar-functions.html#timezone-timezone-timestamp) or [`date_format()`](https://crate.io/docs/crate/reference/en/4.6/general/builtins/scalar-functions.html#date-format-format-string-timezone-timestamp) to convert to a local date time but you will need to type it out in the query if required.

```sql
SELECT
  timezone('Europe/Vienna', now())::timestamptz AS datetime1,
  date_format('%Y-%m-%dT%H:%i:%s.%fZ', 'Europe/Vienna', now()) AS datetime2;

```

I hope this still helps you a bit

---

<div class="post-metadata">

**Author:** ![inviridi](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/inviridi/32/273_2.png) [@inviridi](https://community.cratedb.com/u/inviridi)\
**Post date:** [August 4, 2021, 9:45pm UTC](https://community.cratedb.com/t/timestamp-without-time-zone-in-local-time/765/5 "2021-08-04T21:45:45Z")

</div>

Hi @jayeff,

Many thanks for your swift reply and the code! I will stick to the UTC timestamps if that’s the generally accepted approach. Also, I am happy to report success with your code … it is fine with me to include it my queries. I did some benchmarking already and converting from UTC to another timezone overall adds 5-15ms which is totally acceptable for my application.

Cheers,  
Frank