CODEWITHSUNDEEP
sqlsqlitedatetime
Nuntipat Narkthong
Nuntipat Narkthong

Reputation: 1397

SQLite Data Time Function

I currently have a timestamp in this format Tue Jun 03 17:17:05 +0000 2014 in one column in my table. I want to count the number of records happening in specific intervals (15 minutes). I have tried to follow the answer found in Group records by time. Although my timestamp is in a different format and I haven't seen any support function available in SQLite to convert this. Is this possible in SQL?

Upvotes: 0

Views: 134

Answers (2)

Doug Currie
Doug Currie

Reputation: 41220

The SQLite date and time functions can be used to convert a timestring to a canonical format, or to a Julian Day Number. Unfortunately, the SQLite date and time functions only accept timestring in a limited number of formats:

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

If your timestring format has fixed field widths, you can use the substr function and the || string concatenation operator to convert it to a format SQLite understands. You'll have to use a case expression to convert the month names to numbers; here's an example.

Upvotes: 1

Ram Dwivedi
Ram Dwivedi

Reputation: 470

You may use NEW_TIME in Oracle to convert the time to a specific timezone. Here is an example. This example is converting SYSDATE from PDT to GMT.

SELECT NEW_TIME (SYSDATE, 'PDT', 'GMT') FROM DUAL;

This thread is detailing how to add required minutes to your timestamp.

Upvotes: 0

Related Questions