Date and Time
Date, time, time_ns, interval, interval_ns and datetime are data types based on standard C++ library ‘std::time’. Timestamp is built upon Google’s ‘cctz’ library.
date
(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
time
(since EVL 2.8)
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
time_ns
(since EVL 2.8)
to store a day time with nanoseconds
size: 8 Bytes, range: 00:00:00.000000000 – 23:59:59.999999999
example:13:37:00.350000000
interval
(since EVL 2.8)
to store a time interval in hours, minutes and seconds
size: 4 Bytes, min: 00:00:00
example:165:35:00
interval_ns
(since EVL 2.8)
to store a time interval with nanoseconds
size: 8 Bytes, min: 00:00:00.000000000
example:165:35:00.123456789
datetime
(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
timestamp
(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:
EVL_DEFAULT_DATE_PATTERN
¶to specify default formatting string for ‘date’ data type,
by default it is"%Y-%m-%d"
EVL_DEFAULT_TIME_PATTERN
¶to specify default formatting string for ‘time’ data type,
by default it is"%H:%M:%S"
EVL_DEFAULT_DATETIME_PATTERN
¶to specify default formatting string for ‘datetime’ data type,
by default it is"%Y-%m-%d %H:%M:%S"
EVL_DEFAULT_TIMESTAMP_PATTERN
¶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 ‘%’
%a
locale’s abbreviated weekday name (e.g. ‘Sun’)
%A
locale’s full weekday name (e.g. ‘Sunday’)
%b
locale’s abbreviated month name (e.g. ‘Jan’)
%B
locale’s full month name (e.g. ‘January’)
%c
locale’s date and time (e.g. ‘Thu Mar 3 23:05:25 2005’)
%C
century; like ‘%Y’, except omit last two digits (e.g. ‘20’)
%d
day of month (e.g. ‘01’)
%D
date; same as ‘%m/%d/%y’
%e
day of month, space padded; same as ‘%_d’
%Ez
RFC3339-compatible numeric UTC offset (+hh:mm or -hh:mm)
%E*z
full-resolution numeric UTC offset (+hh:mm:ss or -hh:mm:ss)
%E#S
seconds with # digits of fractional precision
%E*S
seconds with full fractional precision (a literal ’*’)
%E#f
fractional seconds with # digits of precision
%E*f
fractional seconds with full precision (a literal ’*’)
%E4Y
four-character years (-999 ... -001, 0000, 0001 ... 9999)
%ET
the RFC3339 "date-time" separator "T"
%F
full date; same as ‘%Y-%m-%d’
%g
last two digits of year of ISO week number (see ‘%G’)
%G
year of ISO week number (see ‘%V’); normally useful only with ‘%V’
%h
same as ‘%b’
%H
hour (‘00’..‘23’)
%I
hour (‘01’..‘12’)
%j
day of year (‘001’..‘366’)
%k
hour, space padded (‘ 0’..‘23’); same as ‘%_H’
%l
hour, space padded (‘ 1’..‘12’); same as ‘%_I’
%m
month (‘01’..‘12’)
%M
minute (‘00’..‘59’)
%n
a newline
%p
locale’s equivalent of either ‘AM’ or ‘PM’; blank if not known
%P
like ‘%p’, but lower case
%r
locale’s 12-hour clock time (e.g. ‘11:11:04 PM’)
%R
24-hour hour and minute; same as ‘%H:%M’
%s
seconds since ‘1970-01-01 00:00:00 UTC’
%S
second (‘00’..‘60’)
%t
a tab
%T
time; same as ‘%H:%M:%S’
%u
day of week (‘1’..‘7’); ‘1’ is Monday
%U
week number of year, with Sunday as first day of week (‘00’..‘53’)
%V
ISO week number, with Monday as first day of week (‘01’..‘53’)
%w
day of week (‘0’..‘6’); ‘0’ is Sunday
%W
week number of year, with Monday as first day of week (‘00’..‘53’)
%x
locale’s date representation (e.g. ‘12/31/99’)
%X
locale’s time representation (e.g. ‘23:13:48’)
%y
last two digits of year (‘00’..‘99’)
%Y
year
%z
+hhmm numeric time zone (e.g., -0400)
%Z
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
0
(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:
dt.to_date()
returns ‘2017-05-31’, i.e. cut off time and return date data type
dt.to_datetime()
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)’.
Difference:
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