# Datetime conversion

**URL:** <https://community.cratedb.com/t/datetime-conversion/1450>\
**Category:** CrateDB\
**Created:** [April 13, 2023, 9:19am UTC](https://community.cratedb.com/t/datetime-conversion/1450 "2023-04-13T09:19:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vinayak\_Katkar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/vinayak_katkar/32/837_2.png) [@Vinayak\_Katkar](https://community.cratedb.com/u/Vinayak_Katkar)\
**Post date:** [April 13, 2023, 9:19am UTC](https://community.cratedb.com/t/datetime-conversion/1450/1 "2023-04-13T09:19:10Z")

</div>

Hello everyone, Hope all are doing good.

Currently we using cratedb version 4.2.6.  
I want to convert EPOC time to readable date format.  
But column is in array , so when converting array it giving error.

Query is :-

```sql
select orderdata['lrDateTime'] from ap_db_crate.tripdetails_tripcreateddate limit 100;

```

want to convert `lrDateTime` column from epoc to date.

conversion query is

```sql
select date_format('%Y-%m-%dT%H:%i:%s.%fZ' ,orderdata['lrDateTime']) 
from ap_db_crate.tripdetails_tripcreateddate limit 100;

```

error is

```sql
select date_format('%Y-%m-%dT%H:%i:%s.%fZ' ,orderdata['lrDateTime']) 
from ap_db_crate.tripdetails_tripcreateddate limit 100;

```

 ![create_orderdetails](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/ac8936548c0c065097177910a206fd13ab224b04.jpeg)  
 ![error_conversion](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/108d98503898cfbd6bf908c47606cc4b9e95e282.jpeg)

For more details is attach the query with error.  
Thanks  
Vinayak Katkar

---

<div class="post-metadata">

**Author:** ![Vinayak\_Katkar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/vinayak_katkar/32/837_2.png) [@Vinayak\_Katkar](https://community.cratedb.com/u/Vinayak_Katkar)\
**Post date:** [April 13, 2023, 9:38am UTC](https://community.cratedb.com/t/datetime-conversion/1450/2 "2023-04-13T09:38:05Z")

</div>

where lrDateTime is bigInt

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [April 13, 2023, 12:45pm UTC](https://community.cratedb.com/t/datetime-conversion/1450/3 "2023-04-13T12:45:23Z")

</div>

have you tried casting it explicitly to `timestamp` ?

e.g.

```sql
SELECT orderdata['lrDateTime']::timestamptz

```

the error seems strange and actually implies, that it is an `array(bigint)` and not a `bigint`

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [April 13, 2023, 4:28pm UTC](https://community.cratedb.com/t/datetime-conversion/1450/4 "2023-04-13T16:28:57Z")

</div>

If the timestamp is really on an array and you always need the first value you could use

```sql
date_format('%Y-%m-%dT%H:%i:%s.%fZ',orderdata['lrDateTime'][1])

```

---

<div class="post-metadata">

**Author:** ![Vinayak\_Katkar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/vinayak_katkar/32/837_2.png) [@Vinayak\_Katkar](https://community.cratedb.com/u/Vinayak_Katkar)\
**Post date:** [April 19, 2023, 6:45am UTC](https://community.cratedb.com/t/datetime-conversion/1450/5 "2023-04-19T06:45:59Z")

</div>

Hi,  
While execute above got below error.

SELECT orderdata[‘lrDateTime’]::timestamptz :-  
SQLActionException[SQLParseException: Cannot cast expressions from type `bigint_array` to type `timestamp with time zone`]

date\_format(‘%Y-%m-%dT%H:%i:%s.%fZ’,orderdata[‘lrDateTime’][1]) :-  
SQLActionException[UnsupportedFeatureException: Nested array access is not supported]

---

<div class="post-metadata">

**Author:** ![Vinayak\_Katkar](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/vinayak_katkar/32/837_2.png) [@Vinayak\_Katkar](https://community.cratedb.com/u/Vinayak_Katkar)\
**Post date:** [April 19, 2023, 6:50am UTC](https://community.cratedb.com/t/datetime-conversion/1450/6 "2023-04-19T06:50:09Z")

</div>

After using below query  
SELECT cast (unnest (orderdata[‘lrDateTime’])as timestamp)

output is came as per requirement.

Thanks for all your support.