0

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?

Community
  • 1
  • 1
Nuntipat Narkthong
  • 1,387
  • 2
  • 16
  • 24

2 Answers2

1

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.

Community
  • 1
  • 1
Doug Currie
  • 40,708
  • 1
  • 95
  • 119
0

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.

Community
  • 1
  • 1
Ram Dwivedi
  • 470
  • 3
  • 11