[sqlite] strftime et al
Whilst playing with fossil, I have encountered what I believe to be a
deficiency in strftime() (and friends).
As the Sqlite documentation states, dates supplied to the date
functions may have a timezone suffix i.e. "[+-]HH:MM" or just "Z".
The Sqlite input date parsing is faultless to my testing.
The catch is that there is no way to output what one inputs.
e.g. 2013-10-07 04:23:19 -04:00
There is no date_tzoffset('date') function or modifier to perform this task.
The 'utc', 'localtime' modifiers or no modifier enable one to
calculate an offset for a datetime (this offset is internally
calculated and can be calculated with some lengthy SQL).
The proposal:
1. Supply an additional '%z' substitution for strftime().
Without the 'localtime' modifier this will produce the string "Z".
With the 'localtime' modifier this will produce the string "[+-]HH:MM"
2. For the date(), time() and datetime() functions allow an additional
modifier (say 'tz').
This would change datetime() return value as follows:
select datetime('2019-04-21 08:03:07'); -- '2019-04-21 08:03:07'
select datetime('2019-04-21 08:03:07','tz'); -- '2019-04-21 08:03:07Z'
select datetime('2019-04-21 08:03:07','localtime'); -- '2019-04-21 18:03:07'
select datetime('2019-04-21 08:03:07','localtime','tz'); --
'2019-04-21 18:03:07+10:00'
similarly for date() and time().
This change would allow:
select datetime(datetime('now','localtime','tz'));
To produce a correct UTC datetime.
I have a patch.
Prompted by fossil date/time handling.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC stmnt.executeQuery returns empty ResultSet
On 19/04/21 08:25, jordydew...@gmail.com wrote:
> Hi there My Easter is great, what about yours?
Jordy, Mine is going well too, thanks.?n
> I am not pretty sure if that is a thing and it has been a while since I did
> Java, but I have the feeling that you actually should move the statement from
> try(...), so the statement between the parenthesis, within the braces.
While I totally believe that looks odd, I don't normally use this _try_ syntax
normally, it is valid. Not sure when the standard was updated to support that
method.
> Like this: try { PreparedStatement stmnt =
> sqlConnection.prepareStatement("SELECT * FROM entries");
I have moved my statement into the try block, mainly because it helps with
readability.
> Besides, are you 200 percent sure that you are actually loading the right
> database?
I only have a single Database instance in this whole program, the Database class
I wrote works off a singleton design pattern. (there is only one instance of the
main class type).
> Can you go over this entire thing using a debugger?
I am currently running over it with the default debugger used by NetBeans IDE.
However, the packaging of the JDBC library I'm using doesn't provide source code
which I am able to step through. I'm inspecting the variables as I get access to
them, like the ResultSet I get back from the execute function.
> Hope to hear from you and I really hope that helped you a little out there.
It did actually help me realize the database I was connecting to from the cli,
and the one I called from the connection were different, however the problem
persists.
> Regards, Jordy
I actually figured out the problem when I was verifying what I was saying in
this reply. Turns out executeQuery() cannot be called on a PreparedStatement.
This little nugget was hidden at the end of the docs for the method.
Thanks for the help! -Tommy Thanks for the help!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC stmnt.executeQuery returns empty ResultSet
Hi there
My Easter is great, what about yours?
I am not pretty sure if that is a thing and it has been a while since I did
Java, but I have the feeling that you actually should move the statement from
try(...), so the statement between the parenthesis, within the braces.
Like this:
try {
PreparedStatement stmnt = sqlConnection.prepareStatement("SELECT * FROM
entries");
And so forth.
Besides, are you 200 percent sure that you are actually loading the right
database?
Can you go over this entire thing using a debugger?
Hope to hear from you and I really hope that helped you a little out there.
Regards, Jordy
-Oorspronkelijk bericht-
Van: sqlite-users Namens Tommy
Lane
Verzonden: zondag 21 april 2019 18:35
Aan: sqlite-users@mailinglists.sqlite.org
Onderwerp: [sqlite] JDBC stmnt.executeQuery returns empty ResultSet
All,
Hope you Easter is going well.
I am working on a simple journaling application which I'm choosing to
write in Java. Before I get into this description if this is not the place to
ask for help with the sqlite wrapper
(https://bitbucket.org/xerial/sqlite-jdbc/downloads/) please let me know,
although I won't need a plethora of replies to that effect.
When I execute a chunk of code:
ResultSet getTitles() {
ResultSet ret = null;
try (PreparedStatement stmnt =
sqlConnection.prepareStatement("SELECT * FROM entries")) {
ret = stmnt.executeQuery();
} catch (SQLException ex) {
System.out.println("Unable to get entries from the entry
database.");
System.out.println(ex.getMessage());
ex.printStackTrace();
}
return (ret);
}
my rs contains no results.
I've verified that the database which sqlConnection has open does indeed
contain data.
ANY help would be greatly appreciated,
-Tommy
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] JDBC stmnt.executeQuery returns empty ResultSet
All,
Hope you Easter is going well.
I am working on a simple journaling application which I'm
choosing to write in Java. Before I get into this description if this is
not the place to ask for help with the sqlite wrapper
(https://bitbucket.org/xerial/sqlite-jdbc/downloads/) please let me
know, although I won't need a plethora of replies to that effect.
When I execute a chunk of code:
ResultSet getTitles() {
ResultSet ret = null;
try (PreparedStatement stmnt =
sqlConnection.prepareStatement("SELECT * FROM entries")) {
ret = stmnt.executeQuery();
} catch (SQLException ex) {
System.out.println("Unable to get entries from the entry
database.");
System.out.println(ex.getMessage());
ex.printStackTrace();
}
return (ret);
}
my rs contains no results.
I've verified that the database which sqlConnection has open does indeed contain
data.
ANY help would be greatly appreciated,
-Tommy
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
