# timestamp

## Overview

The `timestamp` statement is used to create or update a column containing a timestamp.

The value of the timestamp is derived by applying a template to one or (optionally) two source columns. Timestamps generated by the `timestamp` statement can be UNIX epoch values or yyyyMMdd format strings.

## Syntax

**`timestamp`***`TimeCol`***`[offset`***`secs]`***`using`***`ColName [ColName2]`***`template`***`Template`***`[format yyyymmdd]`**

## Details

The `timestamp` statement populates the *TimeCol* column with either a UNIX timestamp (an integer representing a number of seconds since 00:00:00 on January 1st, 1970) or a yyyyMMdd format string.

If a column called *TimeCol* does not exist, then one will be created. If it does exist then the values within it will be overwritten.

The *ColName* argument must be the name of an existing column which contains date and/or time information to be extracted and used to derive the values in the *TimeCol* column. If the optional *ColName2* argument is present then for each row of data the values in *TimeCol* and *TimeCol2* are concatenated before the extraction of the data is done.

The *TimeCol*, *ColName* and *ColName2* arguments may be [fully qualified](https://olddocs.exivity.io/3.4.3/advanced/digging-deeper/databases) column names, but all three must be located in the same DSET. If *TimeCol* does not exist, then it will be created in the DSET that *ColName* is located in.

A UNIX timestamp will be generated unless `format yyyymmdd` is specified in which case the result will be a yyyyMMdd format timestamp.

If a *secs* parameter is provided and if the output is to be a UNIX timestamp, the specified number of seconds will be added to, or subtracted from, the result. As well as being useful for adjusting for timezones, this permits a time which falls on a midnight boundary to be 'nudged' back 1 second to fall on 23:59:59 of the previous day.

{% hint style="warning" %}
Currently, an offset can only be applied to UNIX format timestamps
{% endhint %}

### Data used by timestamps

In order to create a UNIX timestamp value, the following data is used:

| Field  | Values            | Required | Default |
| ------ | ----------------- | -------- | ------- |
| Year   | `1971` or greater | Yes      | n/a     |
| Month  | `1 - 12`          | No       | `1`     |
| Day    | `1 - 31`          | No       | `1`     |
| Hour   | `0 - 23`          | No       | `0`     |
| Minute | `0 - 59`          | No       | `0`     |
| Second | `0 - 59`          | No       | `0`     |

In order to create a yyyyMMdd timestamp value, the following data is used:

| Field | Values       | Required | Default |
| ----- | ------------ | -------- | ------- |
| Year  | Any 4 digits | Yes      | None    |
| Month | `1 - 12`     | No       | `01`    |
| Day   | `1 - 31`     | No       | `01`    |

In both cases the fields are extracted from the *ColName* and (optionally) *ColName2* columns using the *Template* argument as detailed below.

### Templates

The *Template* argument is a string of characters defining which characters in the *ColName* column (and, if present, the *ColName2* column) are to be extracted in order to obtain the field values shown in the tables above.

The template for a UNIX format timestamp consists of the following characters:

| Character | Meaning         |
| --------- | --------------- |
| `.`       | Any character   |
| `Y`       | A year digit    |
| `M`       | A month digit   |
| `D`       | A day digit     |
| `h`       | An hour digit   |
| `m`       | A minute digit  |
| `s`       | A seconds digit |

{% hint style="info" %}
Upper-case `Y`, `M` and `D` characters are used for the date

Lower-case `h`, `m` and `s` characters are used for the time
{% endhint %}

The template for a yyyyMMdd format timestamp consists of the following characters:

| Character | Meaning       |
| --------- | ------------- |
| `.`       | Any character |
| `Y`       | A year digit  |
| `M`       | A month digit |
| `D`       | A day digit   |

### Source values

For every row in the dataset, the template is applied to a source value which is constructed from the *ColName* and *ColName2* columns as follows:

| ColName     | ColName2    | Source value                                                            |
| ----------- | ----------- | ----------------------------------------------------------------------- |
| *Blank*     | *Blank*     | No action is taken and the row is skipped                               |
| *Not blank* | *Blank*     | The value in *ColName*                                                  |
| *Blank*     | *Not blank* | The value in *ColName2*                                                 |
| *Not blank* | *Not blank* | The value of *ColName* with the value of *ColName2* appended to the end |

When applying the template to the source value the characters in the template are examined one at a time. A dot (`.`) causes the character in the same position in the source value to be ignored. Any of the other template characters will cause the character in the same position in the source value to be extracted and added to one of the fields used to create the timestamp.

Here are some sample template definitions:

| Source value           | Data to extract         | Template              |
| ---------------------- | ----------------------- | --------------------- |
| `15:30:30`             | Hour, Minute and Second | `hh.mm.ss`            |
| `20160701`             | Year, Month and Day     | `YYYYMMDD`            |
| `31-01-2016 17:35:59`  | Full date and time      | `DD.MM.YYYY.hh.mm.ss` |
| `2015-09-01T00:00:00Z` | Year, Month and Day     | `YYYY.MM.DD`          |

{% hint style="info" %}
The length of the template may be shorter than the value that it is being applied to. In the last example shown above, the year, month and date values occur at the start of the string, and the template therefore is only as long as is required to extract them.
{% endhint %}

The template must always contain four `Y` characters to define the year, although they do not have to be consecutive. For all the other characters (apart from `.`) there may be 0 - 2 of them present in the template.

{% hint style="info" %}
If none of any given character is present, then the value will default to the lowest possible value. For example the template `YYYYMM..`, when applied to the input value `20160224` will result in a year of `2016`, a month of `02` and a day of `01` (being the lowest possible value of a day in any given month).
{% endhint %}

### Timestamp generation

Once the above fields have been extracted, they are converted into a UNIX or yyyyMMdd timestamp value as follows:

* If a UNIX timestamp, then it is adjusted for the local time of the Exivity server
* If a yyyyMMdd timestamp then it is treated 'as is'

This value is then placed in the *TimeCol* column. The *TimeCol* column may be the same as *ColName* or *ColName2*.

## Examples

### Example 1 - UNIX format timestamp

Given a dataset of the form:

```
start_date,end_date,start_time,end_time,subscriptionId, ...
20160630,20160630,14:00:00,14:59:59,a9470811-83f2-474b-9523-0ece853d8c3c, ...
20160630,20160630,15:00:00,15:59:59,a9470811-83f2-474b-9523-0ece853d8c3c, ...
```

The statements:

```
timestamp start_time using start_date start_time template "YYYYMMDDhh.mm.ss"
timestamp end_time using end_date end_time template "YYYYMMDDhh.mm.ss"
delete columns start_date end_date
```

Will produce the following result:

```
start_time,end_time,subscriptionId, ...
1467291600,1467295199,a9470811-83f2-474b-9523-0ece853d8c3c, ...
1467295200,1467298799,a9470811-83f2-474b-9523-0ece853d8c3c, ...
```

For verification, the converted values translate back to the following times:

| Timestamp    | Date                              |
| ------------ | --------------------------------- |
| `1467291600` | 30/06/2016, 14:00:00 GMT+1:00 DST |
| `1467295199` | 30/06/2016, 14:59:59 GMT+1:00 DST |
| `1467295200` | 30/06/2016, 15:00:00 GMT+1:00 DST |
| `1467298799` | 30/06/2016, 15:59:59 GMT+1:00 DST |

### Example 2 - yyyyMMdd format timestamp

Given a dataset of the form:

```
subscriptionId,effectiveDate ...
a9470811-83f2-474b-9523-0ece853d8c3c,2015-09-01T00:00:00Z
a9470811-83f2-474b-9523-0ece853d8c3c,2017-01-01T00:00:00Z
```

The statement:

```
timestamp effectiveDate using effectiveDate template YYYY.MM.DD format yyyymmdd
```

Will produce the following result:

```
subscriptionId,effectiveDate ...
a9470811-83f2-474b-9523-0ece853d8c3c,20150901
a9470811-83f2-474b-9523-0ece853d8c3c,20170101
```