• Welcome, Guest. Please login.
 
July 14, 2020, 06:48:38 am

News:

Welcome to the SQLitening support forums!


date and time functions in sqlitening

Started by John Lever, April 28, 2009, 02:40:02 pm

Previous topic - Next topic

John Lever

Can anyone help - stuggling to get started on using sqlitening databases.

Again excuse what may be a v. simplistic question.

What is the best way to store date and time in SQLitening databases fields so it is easy using sqlitening commands to :

1. Select records with dates between selected dates.
2. Select records with dates between one selected date and time and another selected date and time  ?

What SQLITENING calls would be made to do this ?

I am using PowerBasic 9.

Rgds,
John

Paul Squires

Check out the following link. It has a lot of info on SQLite date and time functions:

http://www.sqlite.org/lang_datefunc.html


Fred Meier

I agree with Paul's link.  I store all date-times as "YYYY-MM-DD
HH:MM:SS".  If only need date then "YYYY-MM-DD".  If only need time then
"HH:MM:SS".  This seems to work well in SQLite and also in other
databases. 

I also normally let my SQLite column types default to Column
Affinity of NONE. See http://www.sqlite.org/datatype3.html

Bern Ertl

I like to use the WinAPI FILETIME (quad number) for storing dates and performing math on them.  The WinAPI offers several functions for formatting the dates as strings via SYSTEMTIME structures.  $.02

John Lever

Quote from: TechSupport on April 28, 2009, 06:35:26 pm
Check out the following link. It has a lot of info on SQLite date and time functions:

http://www.sqlite.org/lang_datefunc.html


Dear Support,

This gives functions used in SQLITE - not SQLITENING - can these SQLITE Functions be used in SQLITENING - if so how ?

Paul Squires

May 05, 2009, 11:47:07 am #5 Last Edit: May 05, 2009, 11:49:30 am by TechSupport
SQLitening uses SQLite for the database. That is quite clear. Everything that is available to be done to a SQLite database (via SQL) can be done using SQLitening. SQLitening is simply a thin communication layer built on top of the SQLite database backend. It allows for client/server access across a TCP network. Nothing more, nothing less.

In the Date/Time link provided, there are several SELECT statements in the 'Examples' section that show the date/time functions in use in an SQL query.

For example:

Compute the current date
SELECT date('now');

Compute the last day of the current month.
SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.
SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.
SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.
SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independent.
SELECT julianday('now') - julianday('1776-07-04');

...etc...

You simply need to create your SQL statement and feed it to slSel (for example) and then read the results from the recordset that is returned (refer to ExampleA.bas for a simple example of how this is done).


Bern Ertl

Quote from: John Lever on May 05, 2009, 10:46:34 amThis gives functions used in SQLITE - not SQLITENING - can these SQLITE Functions be used in SQLITENING - if so how ?


There is a difference between SQLite interface functions (ie. the API) and the SQLite implementation of core functions and aggregate functions which are used within SQL statements.  The former are encapsulated by SQLitening.  The latter are used in your SQL statements whether using SQLite or SQLitening.