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

timestampTimeCol[offsetsecs]usingColName [ColName2]templateTemplate[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 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.

Currently, an offset can only be applied to UNIX format timestamps

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

Upper-case Y, M and D characters are used for the date

Lower-case h, m and s characters are used for the time

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

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.

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.

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).

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