CODEWITHSUNDEEP
sqlite
Arian Faurtosh
Arian Faurtosh

Reputation: 18491

How to convert time to seconds with SQLite

I want to be able to store for example 2:30pm in a column in SQLite, and be able to pull that time and convert it to seconds, does SQLite have a function for this?

I was reading http://www.sqlite.org/datatype3.html and they do state they have a time function, but can I do this? I know that SQLite does not have a time datatype, but what type should I store it as then, varchar?

2:30pm --> 52200

I was reading more into this: http://www.sqlite.org/lang_datefunc.html

and it seems like the list of time acceptable is :

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

So does that mean you can't use 2:30pm as a format?

Upvotes: 6

Views: 7038

Answers (1)

v010dya
v010dya

Reputation: 5848

Since you will need the value to be in seconds from the beginning of the day (per your clarification) you can just store as an integer. To convert it to seconds from the beginning of the day rather than 1970 simply subtract midnight of the current day from it.

Something like this strftime('%s','2004-01-01 14:30:00') - strftime('%s','2004-01-01 00:00:00')

If 2:30pm that you are trying to store is current time then you can shorten it to strftime('%s','now') - strftime('%s','2004-01-01 00:00:00')

Upvotes: 6

Related Questions