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:
The statements:
Will produce the following result:
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:
The statement:
Will produce the following result:
Last updated