EVL – QVD Utils

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.

date

(since EVL 1.0)

to store a date, i.e. day, month and year
size: 4 Bytes, range: 1970-01-01 ± approx. 6 × 10¹¹ 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.5)

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

interval

(since EVL 2.5)

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

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 × 10¹¹ 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 × 10¹¹ 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.