sqlite datetime 'now' command

BlitzMax Forums/Brucey's Modules/sqlite datetime 'now' command

jkrankie

(Posted 2008) [#1]

Is the 'now' command working in the sqlite mod? According to the docs here: http://www.sqlite.org/lang_datefunc.html i should be able to use this command to get the current date and time, only it seems to be returning 0.

I've posted the offending code below (pulled straight out of various different places in my project), maybe i am doing something wrong, but i've checked and checked...

Local s3:String = "CREATE TABLE scores (id integer primary key AUTOINCREMENT," + ..
		" playerID integer NOT NULL, " + ..
		" score long," + ..
		" level integer," + ..
		" scoreDate datetime," + ..
		" FOREIGN KEY (playerID) REFERENCES userProfiles(id))"
		'
		sys_Database.executeQuery(s3)
		
		If sys_Database.hasError() Then
			DebugLog("Failed to create scores table") ;
			DebugLog("Terminating Program")
			errorAndClose(sys_Database)
		End If

Local query3:TDatabaseQuery = TDatabaseQuery.Create(sys_Database)
		query3.prepare("INSERT INTO scores values(null,1,3456789,3,'now')")
		query3.execute()

Local query3:TDatabaseQuery = TDatabaseQuery.Create(sys_Database)
	query3 = sys_Database.executeQuery("SELECT * from scores")
	While query3.nextRow()
		Local record:TQueryRecord = query3.rowRecord()
		DebugLog("scoreID: " + record.value(0).getInt())
		DebugLog("playerID: " + record.value(1).getInt())
		DebugLog("score: " + record.value(2).getLong())
		DebugLog("level: " + record.value(3).getInt())
		DebugLog("date/time: " + record.value(4).getDate())
	Wend

if it isn't working, is there a module that can get the current date and time?

Cheers
Charlie

Brucey

(Posted 2008) [#2]

Is the 'now' command working in the sqlite mod?

Ah ha... a trick question :-)

According to the page you linked to, there are "functions" for retrieving date/time information. You probably want to be using datetime(), which the page says, returns "YYYY-MM-DD HH:MM:SS".
So, I think you should call the function in your SQL, with the parameter 'now'.

Here's how I would rewrite your Insert statement :

query3.prepare("INSERT INTO scores values(null,1,3456789,3, DATETIME('now'))")

:o)

jkrankie

(Posted 2008) [#3]

nope, that still doesn't do it. It appears to be valid SQL either way though, as the data is recorded in the table (doesn't record anything if invalid).

Cheers
Charlie

Brucey

(Posted 2008) [#4]

One issue with SQLite is that date type fields are not specified in the database as date type fields, and tend to be returned as strings.

You can use the following code to make yourself a TDBDateTime from a returned string, and return the "Long" data for it :

TDBDateTime.SetFromString(record.getString(4)).getDate()

Or, indeed, use getString() or format() instead, if you'd like it formatted. The format() method lets you use standard date formatting characters to specify which fields to retrieve and in what format.

The TDBDate, TDBDateTime, and TDBTime types are just for convenience if you wish to work with the actual date/time parts, like hour, min, sec, etc.

Brucey

(Posted 2008) [#5]

It appears to be valid SQL either way though

Yes, 'now' is valid SQL. It is a String. Therefore you are actually storing the text "now" in that field.
SQLite doesn't care what data you put in what field. See here : http://www.sqlite.org/datatype3.html

Note that it doesn't mention date/datetime/time on that page.

It does of course make it difficult to know what you are retrieving from the database, unless you already have an idea what kind of data is there :-)

jkrankie

(Posted 2008) [#6]

ooh, that format() one is pretty good :)

cheers Brucey :•)

Cheers
Charlie

TaskMaster

(Posted 2008) [#7]

Now is an SQL keyword, it does not need to be quoted. Take the single quotes away from it.

Brucey

(Posted 2008) [#8]

But to use it in SQLite, you need to wrap it up like this : DATETIME('NOW')

Well, from my searching of the of the documentation. It's not here either : http://www.sqlite.org/lang_keywords.html

:-)

jkrankie

(Posted 2008) [#9]

it doesn't work with out the quotes.

Cheers
Charlie

TaskMaster

(Posted 2008) [#10]

OK, sorry.

I was just assuming, I have never actually used sqlite. Always MSSQL or MySQL.