CODEWITHSUNDEEP
sqlitedatetimecalculated-columnsdayofweekweekday
skeetastax
skeetastax

Reputation: 1718

What's the best way to calculate the weekday?

I have time series data with a TEXT timestamp formatted as DD.MM.YYYY HH:mm:ss.sss (.sss = ms). I extract each component from the TEXT timestamp field and compose an ISO formatted timestamp as YYYY-MM-DD HH:mm:ss.sss.

I read through the documentation on the strftime function but the following doesn't work:

CREATE TABLE tickdata (
    DateTime TEXT, -- source format = "DD.MM.YYYY HH:mm:ss.sss"
    class TEXT,
    category TEXT,
    upper INT,
    lower INT,
    diff INT GENERATED ALWAYS AS (upper - lower) STORED,
    gen_year  INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 7, 4)) STORED,
    gen_month INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 4, 2)) STORED,
    gen_day   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 1, 2)) STORED,
    gen_wkd   INT GENERATED ALWAYS AS CAST(strftime('%w',DateTime) AS INT) STORED, -- syntax error
--  gen_wkd   INT GENERATED ALWAYS AS strftime('%w',DateTime) STORED,       -- also syntax error
--  gen_wkd   CAST(strftime('%w',DateTime) AS INT) GENERATED ALWAYS STORED, -- also syntax error
    gen_hr    INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 12, 2)) STORED,
    gen_min   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 15, 2)) STORED,
    gen_sec   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 18, 2)) STORED,
    gen_ms    INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 21, 3)) STORED,
    gen_ISODate TEXT GENERATED ALWAYS AS (
        FORMAT('%04d', gen_year) || '-' ||
        FORMAT('%02d', gen_month) || '-' ||
        FORMAT('%02d', gen_day) || ' ' ||
        FORMAT('%02d', gen_hr) || ':' ||
        FORMAT('%02d', gen_min) || ':' ||
        FORMAT('%02d', gen_sec) || '.' ||
        FORMAT('%03d', gen_ms) -- format = "YYYY-MM-DD HH:mm:ss.sss"
    ) STORED
);

How can I create a calculated column that stores the day of the week that each row timestamp falls into?

Upvotes: 1

Views: 72

Answers (1)

jhnc
jhnc

Reputation: 16829

As in the comment, you need to wrap parentheses.

Also, you are passing the wrong argument.

gen_wkd INT GENERATED ALWAYS AS (strftime('%w', gen_ISODate)) STORED,

The multiple format + concat could just be one call, and judging by explain output may be more efficient that way:

printf('%04d-%02d-%02d %02d:%02d:%02d.%03d',
       gen_year, gen_month, gen_day,
       gen_hr, gen_min, gen_sec, gen_ms)

(If you mean ISO 8601, shouldn't the space be a T?)

Upvotes: 2

Related Questions