# Create a new \_time column from a short date column stored as string
**URL:** https://community.influxdata.com/t/create-a-new-time-column-from-a-short-date-column-stored-as-string/13834
**Category:** Fluxlang
**Tags:** time, date, flux
**Created:** [April 13, 2020, 1:58pm UTC](https://community.influxdata.com/t/create-a-new-time-column-from-a-short-date-column-stored-as-string/13834 "2020-04-13T13:58:09Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@aastroem](https://community.influxdata.com/u/aastroem)
#### Post date: [April 13, 2020, 1:58pm UTC](https://community.influxdata.com/t/create-a-new-time-column-from-a-short-date-column-stored-as-string/13834/1 "2020-04-13T13:58:09Z")
Hi there,
I’ve got a point in time dataset including information about how many online shopping slots (count) that are available for different stores (chain, name) per date (YYYY-mm-dd).
I would like to make a bar chart with the stacked counts per day and I’d like to remove the current timestamp (\_time) from the table and replace it with a new timestamp compiled using the date. That way I would spread the series out over time again since they are all stacked with the same timestamp (when the measurement was taken).
I’ve managed to get this far:
import “date”
import “strings”
from(bucket: “shopping”)
|\> range(start: -5m)
|\> filter(fn: ® =\> r["\_measurement"] == “supermarket\_slots\_history” and r["\_field"] =~ /count|day|month/)
|\> pivot(rowKey: ["\_time"], columnKey: ["\_field"], valueColumn: “\_value”)
|\> filter(fn: ® =\> r[“month”] == date.month(t: r["\_time"]) and r[“day”] \> date.monthDay(t: r["\_time"]))
|\> filter(fn: ® =\> r[“month”] == date.month(t: r["\_time"]) and r[“day”] \<= date.monthDay(t: r["\_time"])+8)
|\> drop(columns: [“host”, “id”, “day”, “daynumberofweek”, “month”, “\_measurement”])
|\> aggregateWindow(every: 5m, column: “count”, fn: last)
|\> set(key: “newtime”, value: “YYYY-mm-ddT00:00:00Z”)
|\> group(columns: [“chain”, “name”, “date”])
|\> sort(columns: [“date”, “chain”, “name”])
I thought that I could drop the \_time column but I would need to create a new timestamp column first and found the set() function. I’m not sure if I can get the date value within that function though? Maybe concatenate my date value with a static ‘T00:00:00Z’ and feed it to the time() function and use that new timestamp as the \_time column.
I included the strings library to use strings.joinStr but got stuck.
| #group | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | FALSE | TRUE | FALSE | FALSE |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| #datatype | string | long | dateTime:RFC3339 | dateTime:RFC3339 | dateTime:RFC3339 | string | string | string | string | double | string |
| #default | \_result | | | | | | | | | | |
| | result | table | \_time | \_start | \_stop | chain | date | dayofweek | name | count | newtime |
| | | 0 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 14/04/2020 | Tuesday | Regent | 0 | YYYY-mm-ddT00:00:00 |
| | | 1 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 14/04/2020 | Tuesday | Tikipunga | 0 | YYYY-mm-ddT00:00:00 |
| | | 2 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 14/04/2020 | Tuesday | Whangarei | 0 | YYYY-mm-ddT00:00:00 |
| | | 3 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 15/04/2020 | Wednesday | Regent | 0 | YYYY-mm-ddT00:00:00 |
| | | 4 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 15/04/2020 | Wednesday | Tikipunga | 0 | YYYY-mm-ddT00:00:00 |
| | | 5 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 15/04/2020 | Wednesday | Whangarei | 0 | YYYY-mm-ddT00:00:00 |
| | | 6 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 16/04/2020 | Thursday | Regent | 0 | YYYY-mm-ddT00:00:00 |
| | | 7 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 16/04/2020 | Thursday | Tikipunga | 0 | YYYY-mm-ddT00:00:00 |
| | | 8 | 2020-04-13T13:35:00Z | 2020-04-13T13:31:32.804053556Z | 2020-04-13T13:36:32.804053556Z | Countdown | 16/04/2020 | Thursday | Whangarei | 0 | YYYY-mm-ddT00:00:00 |
---
### Author:  [@scott](https://community.influxdata.com/u/scott)
#### Post date: [May 6, 2020, 9:23pm UTC](https://community.influxdata.com/t/create-a-new-time-column-from-a-short-date-column-stored-as-string/13834/2 "2020-05-06T21:23:25Z")
@aastroem I think a simpler solution would be duplicate your `_time` column, then use the [`date.truncate()` function](https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/date/truncate/) to truncate your `newtime` values to a specified unit:
```javascript
import "date"
import "strings"
from(bucket: "shopping")
|> range(start: -5m)
|> filter(fn: (r) => r["_measurement"] == "supermarket_slots_history" and r["_field"] =~ /count|day|month/)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> filter(fn: (r) => r["month"] == date.month(t: r["_time"]) and r["day"] > date.monthDay(t: r["_time"]))
|> filter(fn: (r) => r["month"] == date.month(t: r["_time"]) and r["day"] <= date.monthDay(t: r["_time"])+8)
|> drop(columns: ["host", "id", "day", "daynumberofweek", "month", "_measurement"])
|> aggregateWindow(every: 5m, column: "count", fn: last)
|> duplicate(column: "_time", as: "newtime")
|> map(fn: (r) => ({ r with newtime: date.truncate(t: r.newtime, unit: 1d) }))
|> group(columns: ["chain", "name", "date"])
|> sort(columns: ["date", "chain", "name"])
```