EVL – ETL Tool

Products, services and company names referenced in this document may be either trademarks or registered trademarks of their respective owners.

Copyright © 2017–2022 EVL Tool, s.r.o.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts.

Table of Contents

Date and Time

Date, time, interval and datetime are data types based on standard C++ library ‘std::time’. Timestamp is built upon Google’s ‘cctz’ library.


(since EVL 1.0)

to store a date, i.e. day, month and year
size: 4 Bytes, range: 1970-01-01 ± approx. 6 × 1011 years
first 2 Bytes keeps a year, then 1 Byte for month and 1 Byte for day
example: 2008-04-20


(since EVL 2.7)

to store a day time, i.e. hour, minute and second
size: 4 Bytes, range: 00:00:00 – 23:59:59
example: 13:35:00


(since EVL 2.7)

to store a time interval in hours, minutes and seconds
size: 4 Bytes, min: 00:00:00
example: 165:35:00


(since EVL 1.0 as timestamp, since EVL 2.4 as datetime)

to store a date and time, i.e. year, month, day, hour, minute and second
size: 8 Bytes, range: 1970-01-01 00:00:00 ± approx. 6 × 1011 years
example: 2010-07-01 09:02:00


(since EVL 2.4)

to store a date and time with nanoseconds and with a time zone, i.e. year, month, day, hour, minute, second, nanoseconds and possibly a time zone
size: 12 Bytes, range: 1970-01-01 00:00:00 ± approx. 6 × 1011 years
example: 2015-05-09 13:37:00.000 +02:00

Format string

As an argument (in curly brackets) formatting pattern can be specified. Standard C notation is used.

When no argument to date and time data types are provided, defaults are used:


to specify default formatting string for ‘date’ data type,
by default it is "%Y-%m-%d"


to specify default formatting string for ‘time’ data type,
by default it is "%H:%M:%S"


to specify default formatting string for ‘datetime’ data type,
by default it is "%Y-%m-%d %H:%M:%S"


to specify default formatting string for ‘timestamp’ data type,
by default it is "%Y-%m-%d %H:%M:%E*S"

All possible format strings:


a literal ‘%


locale’s abbreviated weekday name (e.g. ‘Sun’)


locale’s full weekday name (e.g. ‘Sunday’)


locale’s abbreviated month name (e.g. ‘Jan’)


locale’s full month name (e.g. ‘January’)


locale’s date and time (e.g. ‘Thu Mar 3 23:05:25 2005’)


century; like ‘%Y’, except omit last two digits (e.g. ‘20’)


day of month (e.g. ‘01’)


date; same as ‘%m/%d/%y


day of month, space padded; same as ‘%_d


RFC3339-compatible numeric UTC offset (+hh:mm or -hh:mm)


full-resolution numeric UTC offset (+hh:mm:ss or -hh:mm:ss)


seconds with # digits of fractional precision


seconds with full fractional precision (a literal ’*’)


fractional seconds with # digits of precision


fractional seconds with full precision (a literal ’*’)


four-character years (-999 ... -001, 0000, 0001 ... 9999)


the RFC3339 "date-time" separator "T"


full date; same as ‘%Y-%m-%d


last two digits of year of ISO week number (see ‘%G’)


year of ISO week number (see ‘%V’); normally useful only with ‘%V


same as ‘%b


hour (‘00’..‘23’)


hour (‘01’..‘12’)


day of year (‘001’..‘366’)


hour, space padded (‘ 0’..‘23’); same as ‘%_H


hour, space padded (‘ 1’..‘12’); same as ‘%_I


month (‘01’..‘12’)


minute (‘00’..‘59’)


a newline


locale’s equivalent of either ‘AM’ or ‘PM’; blank if not known


like ‘%p’, but lower case


locale’s 12-hour clock time (e.g. ‘11:11:04 PM’)


24-hour hour and minute; same as ‘%H:%M


seconds since ‘1970-01-01 00:00:00 UTC


second (‘00’..‘60’)


a tab


time; same as ‘%H:%M:%S


day of week (‘1’..‘7’); ‘1’ is Monday


week number of year, with Sunday as first day of week (‘00’..‘53’)


ISO week number, with Monday as first day of week (‘01’..‘53’)


day of week (‘0’..‘6’); ‘0’ is Sunday


week number of year, with Monday as first day of week (‘00’..‘53’)


locale’s date representation (e.g. ‘12/31/99’)


locale’s time representation (e.g. ‘23:13:48’)


last two digits of year (‘00’..‘99’)




+hhmm numeric time zone (e.g., -0400)


alphabetic time zone abbreviation (e.g., EDT)

By default, date pads numeric fields with zeroes. The following optional flags may follow ‘%’.


(hyphen) do not pad the field


(underscore) pad with spaces


(zero) pad with zeros


use upper case if possible


use opposite case if possible

EVD Example

Following dates definition are equivalent.

valid_from  date
valid_from  date("%F")
valid_from  date("%Y-%m-%d")

Following datetimes are all the same.

request_dt  datetime
request_dt  datetime("%F %T")
request_dt  datetime("%Y-%m-%d %H:%M:%S")

Following timestamps are all the same.

request_dt  timestamp
request_dt  timestamp("%F %T.%E9f")
request_dt  timestamp("%Y-%m-%d %H:%M:%S.%E9f")

QVD’s format string can be specified:

request_dt  timestamp  qvd:format="%d/%m/%Y %H:%M:%S"
some_date   date       qvd:format="%d.%m.%Y"

Qlik’s time

When time need to be specified in QVD file, then standard timestamp need to be provided, just with ‘qvd:time’ option. Then the date is simply cut off from the timestamp to be stored in QVD:

request_time  timestamp("%H:%M:%S")  qvd:time

Qlik’s interval

When interval data type need to be specified in QVD file, then standard timestamp need to be provided, just with ‘qvd:interval’ option. Then the time is taken since ‘1970-01-01’:

request_time  timestamp("%Y-%m-%d %H:%M:%S")  qvd:interval

Note: Compared to Qlik’s time data type, interval can be larger than 24 hours. For example input timestamp ‘1970-01-02 03:05:30’ would be ‘03:05:30’ as time, but ‘27:05:30’ as interval.

Declaration in mapping

The following declarations are equivalent.

static datetime min_date(1970,1,1,0,0,0);
static datetime min_date("1970-01-01 00:00:00");
static datetime min_date("1970-01-01 00:00:00", "%Y-%m-%d %H:%M:%S");
static datetime min_date = datetime::from_epoch_time(0);
static date min_date(1970,1,1);
static date min_date("1970-01-01");
static date min_date("1970-01-01", "%Y-%m-%d");

Manipulation, comparison

Lets have ‘datetime dt(2017,5,31,19,37,0)’ and ‘date d(2018,1,14)’ in the following examples.

Methods switching date and datetime data type:


returns ‘2017-05-31’, i.e. cut off time and return date data type


returns ‘2018-01-14 00:00:00’, i.e. add ‘00:00:00’ and return datetime data type

Following methods return appropriate values as ‘int’.

dt.year()       -- 2017
 d.year()       -- 2018
dt.month()      -- 5
 d.month()      -- 1
dt.day()        -- 31
 d.day()        -- 14
dt.hour()       -- 19
dt.minute()     -- 37
dt.second()     -- 0
dt.epoch_time() -- 1496169420
dt.yearday()    -- 151
 d.yearday()    -- 14
dt.weekday()    -- 3 (Wednesday)
 d.weekday()    -- 0 (Sunday)

In the context of string, method ‘weekday()’ returns ‘sunday’, ...

weekday()’ returns ‘0’ for Sunday, ‘1’ for Monday, …, ‘6’ for Saturday.

These methods convert date and datetime to string:

string str1 = dt.to_string();    // 2017-05-31 19:37:00,
                                 // i.e. uses default format string
string str2 = dt.to_string("%Y%m%d%H");     // 2017053119
string str3 = min_date.to_string("%Y%m%d"); // 19700101

Comparison:==’, ‘!=’, ‘<’, ‘>’, ‘<=’, ‘>=’.

if (dt >= datetime(1990,1,1) { ... }

Addition, subtraction:

dt += 65;          // add 65 seconds, i.e. 2017-05-31 19:38:05
dt--;              // 2017-05-31 19:38:04
date d(2017,5,31);
d -= 35;           // subtract 35 days, i.e. 2017-04-26

dt.add_year(1);    // 2018-05-31 19:38:04
 d.add_month(-1);  // 2017-03-26
dt.add_day(6);     // 2018-06-06 19:38:04
dt.add_hour(-2);   // 2018-05-31 17:38:04
dt.add_minute(3);  // 2018-05-31 19:41:04
dt.add_second(-6); // 2018-05-31 19:37:58

The difference between ‘dt.add_second(10)’ and ‘dt+10’ is that in the first case we modify the object itself, but in the second case new value is returned. One can use then for example ‘dt.add_hour(2).add_minute(3)’.


auto diff = dt - datetime(2018,5,31,19,36,57); // 61 (seconds)
auto diff =  d - date("2017-04-02");           // -6 (days)

Let’s summarize the logic:

date - int  => date          datetime - int      => datetime
date - date => int           datetime - datetime => int