CODEWITHSUNDEEP
sqlsqlitedatedatetimetime
Bambi Bunny
Bambi Bunny

Reputation: 1428

SQLite - Convert time (like 08:00) into integer

please, I read that best way (and most effective) is saving time as a SMALLINT (INTEGER in real). It is true? I'm building application which using SQLite3 Database and DB size must be small and calculate with date and time (time specially) must be most effective.

I Have this table:

CREATE TABLE events (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    time_start SMALLINT NOT NULL ,
    time_end SMALLINT NOT NULL,
    message_code VARCHAR(64) NOT NULL,
    FOREIGN KEY (message_code) REFERENCES messages (message_code),
    CHECK ((time_start BETWEEN 0 AND 1440) AND (time_end between 0 and 1440) AND (message_code <> ''))
);

But in fact I want inserting values in real time like 08:20 because no one wants calculate that 8 hours is 480 minutes + 20 = 500 minutes.

It is possible how to convert ('08:20') into number 500? I don't need calculate with seconds.

Or do do you think that using DATETIME is a better way?

PS: It's a scheduler which listing a events like a agenda in outlook e.g.

Thank you very much for any advice.

Best regards

Upvotes: 0

Views: 1136

Answers (1)

Alexander Petrov
Alexander Petrov

Reputation: 14261

SQLite has no DateTime data type.

You can store values as char(5) strings - this will only take up five bytes. You can compare these values directly.

sqlite> select '08:20' > '08:30';
0

sqlite> select '08:20' = '08:30';
0

sqlite> select '08:20' < '08:30';
1

0 is false, 1 is true.

And also with the built-in time function:

sqlite> select time('08:20') = time('now');

sqlite> select time('08:20') = time('2018-01-01 05:30:59');

Upvotes: 1

Related Questions