Re: [sqlite] DateTime kind stored as undefined

[mnie]

Hi Cezary,

indeed I try your piece of code (to retrieve data) and it works as expected,
kind is stored in db. It seems that it is a problem with dapper instead of
SqLite. So I will bump issue in Dapper
(https://github.com/StackExchange/Dapper/issues/571). Many thanks for help!

Best regards,
Michał



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Cezary H. Noweta]

Hello,

> my connection string looks like this:
> *var connection = new SQLiteConnection("DateTimeKind=Utc;Data
> Source=:memory:");*
> Here is a blog post about it (settig datetimekind in utc for sqlite) on
> which I based:
> 
https://www.thomaslevesque.com/2015/06/28/how-to-retrieve-dates-as-utc-in-sqlite/


Still, I cannot reproduce te original problem. The following results are OK:

==
test.cs:
==
using System;
using System.Data.SQLite;

class Test
{
static void TestUtc(bool utc, bool ticks)
{
DateTime dtUtcNow = DateTime.UtcNow;
DateTime dt;
SQLiteConnection connection;
SQLiteCommand cmd;
SQLiteDataReader rdr;
int rows;
string connectionstring = "Data Source=:memory:;";

Console.WriteLine("* Testing " + (utc ? "UTC" : "UNSPECIFIED") 
+ "/" + (ticks ? "TICKS" : "ISO8601") + "...");


Console.WriteLine("DateTime: {0}", dtUtcNow);
Console.WriteLine("DateTime.Kind: {0}", dtUtcNow.Kind);

if ( utc ) {
  connectionstring += "DateTimeKind=Utc;";
}
if ( ticks ) {
  connectionstring += "DateTimeFormat=Ticks;";
}
connection = new SQLiteConnection(connectionstring);
connection.Open();
cmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [test] 
([date] DATETIME NOT NULL);");

cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("INSERT INTO [test] ([date]) VALUES 
(@date);");

cmd.Parameters.AddWithValue("@date", dtUtcNow);
cmd.Connection = connection;
rows = cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SQLiteCommand("SELECT [date] FROM [test];");
cmd.Connection = connection;
rdr = cmd.ExecuteReader();

while ( rdr.Read() ) {
dt = rdr.GetDateTime(0);
Console.WriteLine("DateTime: {0}", dt);
Console.WriteLine("DateTime.Kind: {0}", dt.Kind);
}
rdr.Dispose();
cmd.Dispose();
connection.Dispose();
}

static void Main()
{
TestUtc(false, false);
TestUtc(true, false);
TestUtc(false, true);
TestUtc(true, true);
}

}
==

gives the following results:

==
* Testing UNSPECIFIED/ISO8601...
DateTime: 2017-12-16 15:48:39
DateTime.Kind: Utc
DateTime: 2017-12-16 16:48:39
DateTime.Kind: Unspecified
* Testing UTC/ISO8601...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
* Testing UNSPECIFIED/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Unspecified
* Testing UTC/TICKS...
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
DateTime: 2017-12-16 15:48:40
DateTime.Kind: Utc
==

It looks that your app behaves as if it had default settings: 
ISO8601/Unspecified, which results in Local/Unspecified. The sole thing 
I have in mind is Connection Designer. Try to omit the Connection 
Designer -- as far as I remember it had problems with some parameters: 
CD doubled them or had named them incorrectly.


-- best regards

Cezary H. Noweta

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Cezary H. Noweta]

I'm sorry -- the following post was sent to a private e-mail by an accident:

Hello,

On 2017-12-13 12:51, Michał Niegrzybowski wrote:
> I have a table which has a column of type DateTime in my code I insert
> there an actual UTC Date (which is not the same as my local time). When I
> want to gather previously added record, my record contains date in his
> DateTime column, but this DateTime is a localtime with kind specified to
> 'undefined' instead of 'UTC'.


I cannot reproduce the problem. Setting a format to ticks and a kind to 
UTC causes a storing/retrieving a valid UTC DateTime, which is stored as 
INTEGER. Could you provide your connection string?


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime kind stored as undefined

[Simon Slavin]

On 13 Dec 2017, at 11:51am, Michał Niegrzybowski 
 wrote:

> I have a table which has a column of type DateTime in my code I insert
> there an actual UTC Date (which is not the same as my local time). When I
> want to gather previously added record, my record contains date in his
> DateTime column, but this DateTime is a localtime with kind specified to
> 'undefined' instead of 'UTC'.

Can you tell us the datatype of that column ?  If you want to post the CREATE 
TABLE command, that’s fine too.

Can you use the SQLite shell tool to look at the values stored in that field 
without System.Data.SqLite getting in the way ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime to bigint

[Tibor Balog]

Well,

coming from a strongly typed education this is too much freedom for me. - 
Just kidding -


Thanks for the vital info, appreciated.

-Ursprüngliche Nachricht- 
From: Keith Medcalf

Sent: Friday, December 8, 2017 7:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] DateTime to bigint


That is:

UPDATE Table
  SET Datum = (StrfTime('%s', Datum) + 62135596800) * 1000
WHERE typeof(Datum) == 'text';

There is no such thing as a datatype of "bigint".
This declaration will give you a column affinity of "integer".

There is no such thing as a datatype of "DateTime".
This declaration will give you a column affinity of "numeric".

https://sqlite.org/datatype3.html

The TypeOf(...) function tells you the type of a dataitem.  That is, the 
particular data stored in a specific column of the current row.  It says 
nothing about the types of data stored in other columns in the same row, or 
in the same column in other rows.  Every dataitem is individually stored 
using of the the supported types.


Results from TypeOf(...) may be 'null', 'real', 'integer', 'text' or 'blob' 
as these are the only "data types" for a dataitem in SQLite3.


https://sqlite.org/lang_corefunc.html#typeof

The builtin datetime functions do not have any clue what to do with "rata 
die huns" timestamp values so your application will, of course, have to 
handle them itself.  Or you will have to compute a "supported" value for use 
with the builtins (IOS8601 text string *subset*, Unix Timestamp, Julianday 
number in the Propeleptic Gregorian Calendar, for example).  Additionally, 
the datetime values returned by the "localtime"/"UTC" modifier(s) may only 
be correct for 'NOW' where the value of 'NOW' happens to be the current 
instant, if and only if the computer is displaying the correct Zulu and 
Wallclock time of this current instant, depending on the vagaries of time as 
implemented by your platform C library, and such conversions for any time 
that is not the ever continuous streams of the instant 'now' may be 
incorrect, again depending on the platform C library.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
Sent: Friday, 8 December, 2017 10:17
To: SQLite mailing list
Subject: Re: [sqlite] DateTime to bigint

Hi Tibor

Your date format is windows ticks, i.e. 100 nano seconds intervals
since
01/01/0001

You can convert it as follows

SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS
Ticks

where StrfTime('%s', '2004-08-05') is the number of seconds between
the
provided date and 1/1/1970
62135596800 is the difference in seconds between 1/1/1970 and
1/1/0001
and 1000 converts it to nanoseconds

HTH

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 8 December 2017 at 16:53, Tibor Balog <tibor.ba...@gmx.ch> wrote:


Hi there,

I am very new to SQLite but like it sofar.
Run into something couldn’t find an answer may someone can put me

in the

right direction..

Have an application uses SQLite table with a column “Datum” defined

as

bigint.
I have an identical table with column “Datum” as DateTime “-mm-

dd”


How can I convert this column -preferable in place- to bigint?

Some more info:

“2004-08-05” should be converted to “6322726080” .

Thank You,
in advence.
(Tibor)
___
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-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

Re: [sqlite] DateTime to bigint

[Keith Medcalf]

That is:

UPDATE Table
   SET Datum = (StrfTime('%s', Datum) + 62135596800) * 1000
 WHERE typeof(Datum) == 'text';

There is no such thing as a datatype of "bigint".  
This declaration will give you a column affinity of "integer".

There is no such thing as a datatype of "DateTime". 
This declaration will give you a column affinity of "numeric". 

https://sqlite.org/datatype3.html

The TypeOf(...) function tells you the type of a dataitem.  That is, the 
particular data stored in a specific column of the current row.  It says 
nothing about the types of data stored in other columns in the same row, or in 
the same column in other rows.  Every dataitem is individually stored using of 
the the supported types.

Results from TypeOf(...) may be 'null', 'real', 'integer', 'text' or 'blob' as 
these are the only "data types" for a dataitem in SQLite3.

https://sqlite.org/lang_corefunc.html#typeof

The builtin datetime functions do not have any clue what to do with "rata die 
huns" timestamp values so your application will, of course, have to handle them 
itself.  Or you will have to compute a "supported" value for use with the 
builtins (IOS8601 text string *subset*, Unix Timestamp, Julianday number in the 
Propeleptic Gregorian Calendar, for example).  Additionally, the datetime 
values returned by the "localtime"/"UTC" modifier(s) may only be correct for 
'NOW' where the value of 'NOW' happens to be the current instant, if and only 
if the computer is displaying the correct Zulu and Wallclock time of this 
current instant, depending on the vagaries of time as implemented by your 
platform C library, and such conversions for any time that is not the ever 
continuous streams of the instant 'now' may be incorrect, again depending on 
the platform C library.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
>Sent: Friday, 8 December, 2017 10:17
>To: SQLite mailing list
>Subject: Re: [sqlite] DateTime to bigint
>
>Hi Tibor
>
>Your date format is windows ticks, i.e. 100 nano seconds intervals
>since
>01/01/0001
>
>You can convert it as follows
>
>SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS
>Ticks
>
>where StrfTime('%s', '2004-08-05') is the number of seconds between
>the
>provided date and 1/1/1970
>62135596800 is the difference in seconds between 1/1/1970 and
>1/1/0001
>and 1000 converts it to nanoseconds
>
>HTH
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
>Toolkit
>-Forensic Toolkit for SQLite
>email from a work address for a fully functional demo licence
>
>On 8 December 2017 at 16:53, Tibor Balog <tibor.ba...@gmx.ch> wrote:
>
>> Hi there,
>>
>> I am very new to SQLite but like it sofar.
>> Run into something couldn’t find an answer may someone can put me
>in the
>> right direction..
>>
>> Have an application uses SQLite table with a column “Datum” defined
>as
>> bigint.
>> I have an identical table with column “Datum” as DateTime “-mm-
>dd”
>>
>> How can I convert this column -preferable in place- to bigint?
>>
>> Some more info:
>>
>> “2004-08-05” should be converted to “6322726080” .
>>
>> Thank You,
>> in advence.
>> (Tibor)
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime to bigint

[Tibor Balog]

Bow!

that's what I call 'service' !

I was looking for a good day for this and nothing habe poped up only for the 
reverse task.


You made my day!

Thank You Sir.

-Ursprüngliche Nachricht- 
From: Paul Sanderson

Sent: Friday, December 8, 2017 6:16 PM
To: SQLite mailing list
Subject: Re: [sqlite] DateTime to bigint

Hi Tibor

Your date format is windows ticks, i.e. 100 nano seconds intervals since
01/01/0001

You can convert it as follows

SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS Ticks

where StrfTime('%s', '2004-08-05') is the number of seconds between the
provided date and 1/1/1970
62135596800 is the difference in seconds between 1/1/1970 and 1/1/0001
and 1000 converts it to nanoseconds

HTH

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 8 December 2017 at 16:53, Tibor Balog <tibor.ba...@gmx.ch> wrote:


Hi there,

I am very new to SQLite but like it sofar.
Run into something couldn’t find an answer may someone can put me in the
right direction..

Have an application uses SQLite table with a column “Datum” defined as
bigint.
I have an identical table with column “Datum” as DateTime “-mm-dd”

How can I convert this column -preferable in place- to bigint?

Some more info:

“2004-08-05” should be converted to “6322726080” .

Thank You,
in advence.
(Tibor)
___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime to bigint

[Paul Sanderson]

Hi Tibor

Your date format is windows ticks, i.e. 100 nano seconds intervals since
01/01/0001

You can convert it as follows

SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS Ticks

where StrfTime('%s', '2004-08-05') is the number of seconds between the
provided date and 1/1/1970
62135596800 is the difference in seconds between 1/1/1970 and 1/1/0001
and 1000 converts it to nanoseconds

HTH

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 8 December 2017 at 16:53, Tibor Balog  wrote:

> Hi there,
>
> I am very new to SQLite but like it sofar.
> Run into something couldn’t find an answer may someone can put me in the
> right direction..
>
> Have an application uses SQLite table with a column “Datum” defined as
> bigint.
> I have an identical table with column “Datum” as DateTime “-mm-dd”
>
> How can I convert this column -preferable in place- to bigint?
>
> Some more info:
>
> “2004-08-05” should be converted to “6322726080” .
>
> Thank You,
> in advence.
> (Tibor)
> ___
> 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

Re: [sqlite] Datetime / Transactions / CLI

[Stephen Chrzanowski]

Although I don't do many long length transactions for date and times, I
kind of like the idea of having the control of over how the library allows
you to chose which way the dates and times are going to work. Default to
the current model, of course, to allow for backward compatibility, but,
either allow for a pragma to be set a particular way, or, a new compile
time directive to configure for the required mode.

On Tue, Nov 28, 2017 at 2:25 PM,  wrote:

>
>
> My original comments/requests to the developers still apply though:
>
> - Can the documentation be updated to include something like
>   Keith's description; and
> - Can we have some kind of control over whether 'now' is
>   step-/statement-/real-time
>
> Regards,
> Mark
> --
> Mark Lawrence
> ___
> 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

Re: [sqlite] Datetime / Transactions / CLI

[nomad]

On Tue Nov 28, 2017 at 10:34:03AM -0700, Keith Medcalf wrote:
> 
> Datetime functions (that is, what constitutes "now") was, by default,
> step-stable.  The value is cached within the VDBE (statement object)
> on its first use per-step and retains the same value until the VDBE
> code yields a row.  Re-entry on the next step used to reset "now".
> 
> This was changed to per-statement stability and not per-step
> stability, though exactly when this change occurred I do not recall
> exactly (I believe it was made a SLOCHNG function when indexes on
> functions were added).
> 
> As I posted earlier you can patch the VDBE code to ensure that "now"
> is always statement stable rather than step stable by only resetting
> "now" on initial entry to the VDBE program (program counter == 0)
> rather than unconditionally.  Of course, the change that made "now"
> statement stable renders this patch moot.
> 
> In no case is now "transaction stable" as the value of now is cached
> within the statement object (VDBE program) and not in the connection
> object.

Thanks, that is quite informative. My original observations
(transaction stable times) were wrong - I was looking at a bunch of
trigger actions and forgot they were all part of one statement.

My original comments/requests to the developers still apply though:

- Can the documentation be updated to include something like
  Keith's description; and
- Can we have some kind of control over whether 'now' is
  step-/statement-/real-time

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime / Transactions / CLI

[Simon Slavin]

On 28 Nov 2017, at 5:34pm, Keith Medcalf  wrote:

> This would indicate that "now" has statement-stability and not 
> transaction-stability, which matches with my observations.

You’re right, I was wrong.  Thanks for the correction.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime / Transactions / CLI

[Keith Medcalf]

Datetime functions (that is, what constitutes "now") was, by default, 
step-stable.  The value is cached within the VDBE (statement object) on its 
first use per-step and retains the same value until the VDBE code yields a row. 
 Re-entry on the next step used to reset "now".

This was changed to per-statement stability and not per-step stability, though 
exactly when this change occurred I do not recall exactly (I believe it was 
made a SLOCHNG function when indexes on functions were added).

As I posted earlier you can patch the VDBE code to ensure that "now" is always 
statement stable rather than step stable by only resetting "now" on initial 
entry to the VDBE program (program counter == 0) rather than unconditionally.  
Of course, the change that made "now" statement stable renders this patch moot.

In no case is now "transaction stable" as the value of now is cached within the 
statement object (VDBE program) and not in the connection object.

This case is where I have "forced" "now" to be statement stable ... although 
removing that patch appears to make no difference.
Note this is with the current head of trunk.

The current source code contains the following comment (search the amalgamation 
for iCurrentTime):

/*
** Return the current time for a statement.  If the current time
** is requested more than once within the same run of a single prepared
** statement, the exact same time is returned for each invocation regardless
** of the amount of time that elapses between invocations.  In other words,
** the time returned is always the time of the first call.
*/

This would indicate that "now" has statement-stability and not 
transaction-stability, which matches with my observations.

import apsw
import apswrow
import time
db = apsw.Connection(':memory:')
db.cursor().execute('begin immediate');
while True:
 for row in db.cursor().execute('select value, julianday() from generate_series 
where start=1 and stop=10'):
  print row
  time.sleep(1)

Row(value=1, julianday=2458086.2071575928)
Row(value=2, julianday=2458086.2071575928)
Row(value=3, julianday=2458086.2071575928)
Row(value=4, julianday=2458086.2071575928)
Row(value=5, julianday=2458086.2071575928)
Row(value=6, julianday=2458086.2071575928)
Row(value=7, julianday=2458086.2071575928)
Row(value=8, julianday=2458086.2071575928)
Row(value=9, julianday=2458086.2071575928)
Row(value=10, julianday=2458086.2071575928)
Row(value=1, julianday=2458086.2072733566)
Row(value=2, julianday=2458086.2072733566)
Row(value=3, julianday=2458086.2072733566)
Row(value=4, julianday=2458086.2072733566)
Row(value=5, julianday=2458086.2072733566)
Row(value=6, julianday=2458086.2072733566)
Row(value=7, julianday=2458086.2072733566)
Row(value=8, julianday=2458086.2072733566)
Row(value=9, julianday=2458086.2072733566)
Row(value=10, julianday=2458086.2072733566)
Row(value=1, julianday=2458086.2073891205)
Row(value=2, julianday=2458086.2073891205)
Row(value=3, julianday=2458086.2073891205)
Row(value=4, julianday=2458086.2073891205)
Row(value=5, julianday=2458086.2073891205)
Row(value=6, julianday=2458086.2073891205)
Row(value=7, julianday=2458086.2073891205)
Row(value=8, julianday=2458086.2073891205)
Row(value=9, julianday=2458086.2073891205)
Row(value=10, julianday=2458086.2073891205)
Row(value=1, julianday=2458086.207504896)
Row(value=2, julianday=2458086.207504896)



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Tuesday, 28 November, 2017 09:02
>To: SQLite mailing list
>Subject: Re: [sqlite] Datetime / Transactions / CLI
>
>
>
>On 28 Nov 2017, at 3:50pm, no...@null.net wrote:
>
>> Can someone point me to the documentation for behaviour of
>date/time
>> functions inside transactions? In my code it appears time is
>frozen.
>
>Correct.  The value of 'now' is frozen at the time a transaction
>begins.  This is to ensure that if many rows are created/updated
>(perhaps even in different tables) they are all assigned the same
>timestamp.
>
>If you need to record, instead, the timestamp that data was entered
>into your user-interface, then you need to record that time in your
>programming language, not rely on when the data hits SQL.
>
>> The command-line client on the other hand doesn't behave the same
>way:
>>
>>  sqlite> begin immediate;
>>  sqlite> select julianday();
>>  julianday()
>>  
>>  2458086.15509343
>>  sqlite> select julianday();
>>  julianday()
>>  
>>  2458086.15511422
>>
>> I'm guessing that the CLI is kind of br

Re: [sqlite] Datetime / Transactions / CLI

[Simon Slavin]

On 28 Nov 2017, at 3:50pm, no...@null.net wrote:

> Can someone point me to the documentation for behaviour of date/time
> functions inside transactions? In my code it appears time is frozen.

Correct.  The value of 'now' is frozen at the time a transaction begins.  This 
is to ensure that if many rows are created/updated (perhaps even in different 
tables) they are all assigned the same timestamp.

If you need to record, instead, the timestamp that data was entered into your 
user-interface, then you need to record that time in your programming language, 
not rely on when the data hits SQL.

> The command-line client on the other hand doesn't behave the same way:
> 
>   sqlite> begin immediate;
>   sqlite> select julianday();
>   julianday()
>   
>   2458086.15509343
>   sqlite> select julianday();
>   julianday()
>   
>   2458086.15511422
> 
> I'm guessing that the CLI is kind of broken

That’s an interesting observation and I am surprised by it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime vs strftime?

[Clemens Ladisch]

Andy Bradford wrote:
> sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S',1);
> -471-11-25 12:00:00
>
> Is this perhaps undefined behavior because it does say %Y has a range of
> --?

Yes.  (strftime always outputs the year with four characters, whatever
they might be.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime, its customary column affinity, and storage class info

[John Gabriele]

On Mon, Oct 22, 2012 at 11:38 PM, Simon Slavin  wrote:
>
> On 23 Oct 2012, at 3:42am, John Gabriele  wrote:
>
>> Which column affinity is most customary to use for storing "-MM-DD
>> HH:MM:SS" datetime values?
>
> Text.  They are just text.  As you've figured out, SQLite has no datetime 
> datatype.
> ...

Thanks, Richard, Igor, and Simon! Very clear now. Much appreciated. :)

(Incidentally, I'm also grateful for the examples in the docs at
http://www.sqlite.org/lang_datefunc.html . They make a nice addition,
IMO, to the prose and the diagrams in the documentation. And it's also
handy that I can copy/paste them right into my `sqlite>` prompt to try
them out.)

---John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime, its customary column affinity, and storage class info

[Simon Slavin]

On 23 Oct 2012, at 3:42am, John Gabriele  wrote:

> Which column affinity is most customary to use for storing "-MM-DD
> HH:MM:SS" datetime values?

Text.  They are just text.  As you've figured out, SQLite has no datetime 
datatype.

> The docs at http://sqlite.org/datatype3.html , section 1.2, say "the
> built-in Date And Time Functions of SQLite are capable of storing
> dates and times as TEXT, REAL, or INTEGER values: ... INTEGER as Unix
> Time", so, I'd expected "that_date" to be a large integer, for
> example, like this:
> 
> 1|2012-10-23 02:26:03|1350959558|2012-10-23 02:26:03

The function you used, datetime('now'), returns a text result.  If you want 
that value converted into a number you have to make it happen either in your 
own code or by using SQLite functions, perhaps

julianday('now')

OR

datetime('now', 'unixepoch').  See many examples in



> Also, tangentially-related question: does each value in a row have its
> own storage class? Is it a separate bit of data associated with (and
> stored somewhere for) every single item? Is there a way I can ask
> sqlite what's the storage class of a given element of data?

Yes.  Yes.  And see typeof() in



for instance

SELECT typeof(datetime('now'))

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime, its customary column affinity, and storage class info

[Igor Tandetnik]

John Gabriele  wrote:
> Which column affinity is most customary to use for storing "-MM-DD
> HH:MM:SS" datetime values?

Text.

> I tried this:
> 
> ~~~sql
> create table t1 (
>  id integer primary key,
>  this_date text,
>  that_date int,
>  other_date none);
> 
> insert into t1 (this_date, that_date, other_date)
>  values (datetime('now'), datetime('now'), datetime('now'));
> 
> select * from t1;
> ~~~
> 
> and the output is the same for all three columns:
> 
> 1|2012-10-23 02:26:03|2012-10-23 02:26:03|2012-10-23 02:26:03

You inserted text values in all three columns. Run this statement, see for 
yourself:

select typeof(this_date), typeof(that_date), typeof(other_date);

> The docs at http://sqlite.org/datatype3.html , section 1.2, say "the
> built-in Date And Time Functions of SQLite are capable of storing
> dates and times as TEXT, REAL, or INTEGER values

Yes. REAL for julian dates, INTEGER for seconds since Unix epoch, TEXT for 
strings of the form you are asking about.

> so, I'd expected "that_date" to be a large integer

Why? datetime() returns a string. If you want an integer, make it 
strftime('%s', 'now')

You seem to believe that SQLite date/time functions return some special data 
type that can be automatically converted to either text or integer. That is not 
the case - there is no dedicated type for date/time values.

> It would seem that the value returned by
> datetime('now') would be coerced to int

A string like '2012-10-23 02:26:03' doesn't resemble a valid textual 
representation of any integer.

> Also, tangentially-related question: does each value in a row have its
> own storage class?

Generally, yes.

> Is it a separate bit of data associated with (and stored somewhere for) every 
> single item?

Yes. SQLite file format is documented here: http://sqlite.org/fileformat2.html

> Is there a way I can ask
> sqlite what's the storage class of a given element of data?

typeof, sqlite3_column_type

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime, its customary column affinity, and storage class info

[Richard Hipp]

On Mon, Oct 22, 2012 at 10:42 PM, John Gabriele  wrote:

> Hi,
>
> Which column affinity is most customary to use for storing "-MM-DD
> HH:MM:SS" datetime values?
>

I always use DATE or DATETIME or TIME, depending on what I'm storing.  I
believe these always have affinity of NONE.


>
> I tried this:
>
> ~~~sql
> create table t1 (
>   id integer primary key,
>   this_date text,
>   that_date int,
>   other_date none);
>
> insert into t1 (this_date, that_date, other_date)
>   values (datetime('now'), datetime('now'), datetime('now'));
>
> select * from t1;
> ~~~
>
> and the output is the same for all three columns:
>
> 1|2012-10-23 02:26:03|2012-10-23 02:26:03|2012-10-23 02:26:03
>
> The docs at http://sqlite.org/datatype3.html , section 1.2, say "the
> built-in Date And Time Functions of SQLite are capable of storing
> dates and times as TEXT, REAL, or INTEGER values: ... INTEGER as Unix
> Time", so, I'd expected "that_date" to be a large integer, for
> example, like this:
>
> 1|2012-10-23 02:26:03|1350959558|2012-10-23 02:26:03
>
> but it does not. It would seem that the value returned by
> datetime('now') would be coerced to int since that's the affinity of
> the column I'm putting it in...
>


You are way over-thinking his.

Conversions between numeric and text only occur if the result "looks" the
same when printing.  so '12345' (text) will convert to 12345 (integer) and
back again.  Affinity never changes the "look" of a value.

So never would affinity cause '2012-10-23 02:26:03' to be converted into
1350959163 because those two things do not look the same.  If you need to
convert from ISO-8601 into unix-time, then do:

 strftime('%s', '2012-10-03 02:26:03');

And if you need to convert from unix-time to ISO-8601, using this:

 datetime(1350959163, 'unixepoch');

The following should always be a no-op:

 datetime(strftime('%s', dateHere), 'unixepoch')



>
> Also, tangentially-related question: does each value in a row have its
> own storage class? Is it a separate bit of data associated with (and
> stored somewhere for) every single item? Is there a way I can ask
> sqlite what's the storage class of a given element of data?
>
> Thanks!
> ---John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime question

[Keith Medcalf]

> create table test( Date datetime);

Datetime is not a data type and therefore has numeric affinity.

> insert test now();

sqlite> select now();
Error: no such function: now

What is function now and what does it return?

> select date+2 as bbb,date-12 as cc from test;
> the result is

> 2014,2000

>From this I can surmise that your "now()" is the number 2012.

> I wanna know how to add any day use sql command

First you need to store an application specific representation of the datetime 
value in the "date" column.  Then you apply the function to that value that 
achieves the result you desire.  For example, if you store an MJD value in 
"Date" then you add or subtract fractional days.  If you store a unix-epoch 
value, then adding/subtracting 86400 moves forward and back a day.  There are 
builtin functions to help with some of this.

You might want to refer to:

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

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of YAN HONG YE
> Sent: Wednesday, 22 August, 2012 20:14
> To: sqlite-users@sqlite.org
> Subject: [sqlite] datetime question
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime question

[Simon Slavin]

On 23 Aug 2012, at 3:14am, YAN HONG YE  wrote:

> create table test( Date datetime);
> insert test now();
> select date+2 as bbb,date-12 as cc from test;
> the result is 
> 2014,2000
> I wanna know how to add any day use sql command

There is no field type 'datetime'.  Dates are often expressed as TEXT.

Do not use 'Date' as a fieldname because the word 'Date' is used by SQLite for 
a keyword.

sqlite> CREATE TABLE test (thisDate TEXT);
sqlite> INSERT INTO test (thisDate) VALUES (date('now'));
sqlite> SELECT * from test;
2012-08-23
sqlite> SELECT date(thisDate,'+2 day'), date(thisDate,'-12 day') FROM test;
2012-08-25|2012-08-11

For documentation see this page:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Stephen Chrzanowski]

That would make sense.  Thank you for clearing that up.

On Tue, Dec 20, 2011 at 8:50 AM, Igor Tandetnik  wrote:

> Stephen Chrzanowski  wrote:
> > I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should
> be
> > 13:06Z.  However, according to this:
> >
> > select datetime('now','localtime'),datetime('now','utc');
> > datetime('now','localtime')datetime('now','utc')
> > 2011-12-20 08:05:242011-12-20 18:05:24
>
> You misunderstand how modifiers work. datetime('now'), with no modifier,
> produces a string reflecting UTC time. 'localtime' modifier assumes that
> the string to the left of it represents time in UTC, and converts it to
> local time - so far so good. 'utc' does the reverse - it assumes that the
> string to the left is in local time, and converts it to UTC. But since
> 'now' is already in UTC, you effectively apply the time zone bias twice -
> that's how you end up with 10 hours difference.
>
> > One thing I JUST tried now is the following:
> >
> > select datetime('now','localtime'),datetime('now','localtime','utc');
> > datetime('now','localtime')datetime('now','localtime','utc')
> > 2011-12-20 08:10:432011-12-20 13:10:43
>
> Naturally, since 'localtime' and 'utc' do the exact opposite adjustments,
> datetime('now','localtime','utc') is equivalent to datetime('now'). It's
> like being surprised that -(-1) == 1
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Igor Tandetnik]

Stephen Chrzanowski  wrote:
> I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should be
> 13:06Z.  However, according to this:
> 
> select datetime('now','localtime'),datetime('now','utc');
> datetime('now','localtime')datetime('now','utc')
> 2011-12-20 08:05:242011-12-20 18:05:24

You misunderstand how modifiers work. datetime('now'), with no modifier, 
produces a string reflecting UTC time. 'localtime' modifier assumes that the 
string to the left of it represents time in UTC, and converts it to local time 
- so far so good. 'utc' does the reverse - it assumes that the string to the 
left is in local time, and converts it to UTC. But since 'now' is already in 
UTC, you effectively apply the time zone bias twice - that's how you end up 
with 10 hours difference.

> One thing I JUST tried now is the following:
> 
> select datetime('now','localtime'),datetime('now','localtime','utc');
> datetime('now','localtime')datetime('now','localtime','utc')
> 2011-12-20 08:10:432011-12-20 13:10:43

Naturally, since 'localtime' and 'utc' do the exact opposite adjustments, 
datetime('now','localtime','utc') is equivalent to datetime('now'). It's like 
being surprised that -(-1) == 1
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Stephen Chrzanowski]

That returned the expected results.  So in other words, even though 'UTC'
is a valid option, it shouldn't be used?

On Tue, Dec 20, 2011 at 8:41 AM, Kit  wrote:

> 2011/12/20 Stephen Chrzanowski :
> > I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should
> be
> > 13:06Z.  However, according to this:
> >
> > select datetime('now','localtime'),datetime('now','utc');
> > datetime('now','localtime')datetime('now','utc')
> > 2011-12-20 08:05:242011-12-20 18:05:24
>
> Try
> select datetime('now');
> --
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime

[Kit]

2011/12/20 Stephen Chrzanowski :
> I live in GMT-5 (America/Toronto).  Current time is 8:06am, which should be
> 13:06Z.  However, according to this:
>
> select datetime('now','localtime'),datetime('now','utc');
> datetime('now','localtime')    datetime('now','utc')
> 2011-12-20 08:05:24            2011-12-20 18:05:24

Try
select datetime('now');
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime('now') has only per-second resolution?

[Igor Tandetnik]

On 8/4/2011 11:35 AM, Sean Hammond wrote:
> Hey, I've been recording timestamped log messages in sqlite3 by using
> datetime('now') in INSERT queries, e.g.:
>
> INSERT INTO Logs (...,time) VALUES (...,datetime('now'));
>
> (The time column has type DATETIME.)

That's irrelevant. You are storing strings. SQLite doesn't have a 
dedicated timestamp format. See http://sqlite.org/datatype3.html

> I noticed that if I retrieve these rows with a SELECT query the
> datetimes only have per-second resolution, no fractions of a second,
> e.g.: "2011-07-31 16:04:48"

datetime(x) is just a shorthand for strftime('%Y-%m-%d %H:%M:%S', x)

> I tried to retrieve fractions of a second with a SELECT query containing
> a strftime with %f, e.g.:
>
> SELECT strftime("%Y-%m-%d %H:%M:%f",time) FROM Logs;
>
> but in the table returned all of the fractions of seconds are 000.

Where would non-zero milliseconds come from? They are not stored with 
the data.

> Am I correct in thinking that sqlite3 stores datetime('now') as a string
> without milliseconds?

To be precise, datetime('now') returns a string in a format that doesn't 
include milliseconds. You then store this string in the table.

> Are the resolutions of sqlite's date and time
> functions explicitly documented anywhere?

http://sqlite.org/lang_datefunc.html

> The solution seems to be to use strftime with %f in the INSERT query:
>
> INSERT INTO Logs (...,time) VALUES (...,strftime('%Y-%m-%d
> %H:%M:%f','now'));

That's one way to do it, yes.

> then the results from SELECT queries will contain fractions of a second.

Of course. You get back out exactly what you put in.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[Peter van Dijk]

On Oct 8, 2009, at 16:16 , Jay A. Kreibich wrote:

> On Thu, Oct 08, 2009 at 08:29:10AM +0200, Fredrik Karlsson scratched  
> on the wall:
>
>> Yes, that would have been my guess too, but I am on CET, which I
>> understand is UTC+1.

CET is CEST in summer, which is UTC+2

Cheers, Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[Jay A. Kreibich]

On Thu, Oct 08, 2009 at 08:29:10AM +0200, Fredrik Karlsson scratched on the 
wall:

> Yes, that would have been my guess too, but I am on CET, which I
> understand is UTC+1.

   The timestamp on your own email tells a different story:

 Date: Thu, 8 Oct 2009 08:29:10 +0200
 From: Fredrik Karlsson <dargo...@gmail.com>
 To: punk...@eidesis.org,
General Discussion of SQLite Database <sqlite-users@sqlite.org>
 Subject: Re: [sqlite] Datetime mystery


  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[Fredrik Karlsson]

Hi,

Yes! That's it! Sorry about the stupid question then..
select datetime('now','localtime'); seems to do what I want.


/Fredrik

On Thu, Oct 8, 2009 at 9:39 AM, Simon Davies
 wrote:
> 2009/10/8 Fredrik Karlsson :
>> Hi,
>>
>>
>>
>> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor  wrote:
>>> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson  wrote:
 Dear list,

 I am sorry if I am asking a FAQ, but what is differnent with
 datetime() and time()?

> date # This is the correct time on the system
 Ons  7 Okt 2009 23:56:36 CEST
> sqlite3 temp.sqlite "SELECT datetime();"
 2009-10-07 21:56:58
> sqlite3 temp.sqlite "SELECT datetime('now);"
 SQL error: unrecognized token: "'now);"
> sqlite3 temp.sqlite "SELECT datetime('now');"
 2009-10-07 21:57:13
> sqlite3 temp.sqlite "SELECT time('now');"
 21:59:05

 What happened here? How come the time functions are off 2 hours?
 (I am using sqlite version 3.5.9 on a Mac OS Leopard machine)

>>>
>>>
>>> time zones. The sqlite returned times, by default, are UTC.
>>>
>>
>> Yes, that would have been my guess too, but I am on CET, which I
>> understand is UTC+1. So, I am still getting one hour less than I
>> should from SQLite. Or, am I doing something stupid?
>
> From above, you are currently on CEST, which is UTC+2
>
>>
>> /Fredrik
>> "Life is like a trumpet - if you don't put anything into it, you don't
>> get anything out of it."
>
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[Simon Davies]

2009/10/8 Fredrik Karlsson :
> Hi,
>
>
>
> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor  wrote:
>> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson  wrote:
>>> Dear list,
>>>
>>> I am sorry if I am asking a FAQ, but what is differnent with
>>> datetime() and time()?
>>>
 date # This is the correct time on the system
>>> Ons  7 Okt 2009 23:56:36 CEST
 sqlite3 temp.sqlite "SELECT datetime();"
>>> 2009-10-07 21:56:58
 sqlite3 temp.sqlite "SELECT datetime('now);"
>>> SQL error: unrecognized token: "'now);"
 sqlite3 temp.sqlite "SELECT datetime('now');"
>>> 2009-10-07 21:57:13
 sqlite3 temp.sqlite "SELECT time('now');"
>>> 21:59:05
>>>
>>> What happened here? How come the time functions are off 2 hours?
>>> (I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
>>>
>>
>>
>> time zones. The sqlite returned times, by default, are UTC.
>>
>
> Yes, that would have been my guess too, but I am on CET, which I
> understand is UTC+1. So, I am still getting one hour less than I
> should from SQLite. Or, am I doing something stupid?

>From above, you are currently on CEST, which is UTC+2

>
> /Fredrik
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."


Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[Fredrik Karlsson]

Hi,



On Thu, Oct 8, 2009 at 12:04 AM, P Kishor  wrote:
> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson  wrote:
>> Dear list,
>>
>> I am sorry if I am asking a FAQ, but what is differnent with
>> datetime() and time()?
>>
>>> date # This is the correct time on the system
>> Ons  7 Okt 2009 23:56:36 CEST
>>> sqlite3 temp.sqlite "SELECT datetime();"
>> 2009-10-07 21:56:58
>>> sqlite3 temp.sqlite "SELECT datetime('now);"
>> SQL error: unrecognized token: "'now);"
>>> sqlite3 temp.sqlite "SELECT datetime('now');"
>> 2009-10-07 21:57:13
>>> sqlite3 temp.sqlite "SELECT time('now');"
>> 21:59:05
>>
>> What happened here? How come the time functions are off 2 hours?
>> (I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
>>
>
>
> time zones. The sqlite returned times, by default, are UTC.
>

Yes, that would have been my guess too, but I am on CET, which I
understand is UTC+1. So, I am still getting one hour less than I
should from SQLite. Or, am I doing something stupid?

/Fredrik
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime mystery

[P Kishor]

On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson  wrote:
> Dear list,
>
> I am sorry if I am asking a FAQ, but what is differnent with
> datetime() and time()?
>
>> date # This is the correct time on the system
> Ons  7 Okt 2009 23:56:36 CEST
>> sqlite3 temp.sqlite "SELECT datetime();"
> 2009-10-07 21:56:58
>> sqlite3 temp.sqlite "SELECT datetime('now);"
> SQL error: unrecognized token: "'now);"
>> sqlite3 temp.sqlite "SELECT datetime('now');"
> 2009-10-07 21:57:13
>> sqlite3 temp.sqlite "SELECT time('now');"
> 21:59:05
>
> What happened here? How come the time functions are off 2 hours?
> (I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
>


time zones. The sqlite returned times, by default, are UTC.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Wilson, Ronald]

> According to http://www.sqlite.org/lang_datefunc.html datetime('now')
> returns date and time already as UTC. If you add 'utc' modifier then
> it makes datetime() think that it's your local time and convert it to
> 'utc' thus adding 4 hours (apparently you're in GMT -4 timezone).

Thanks.  I figured out this nuance late last night.  (Actually I'm in
GMT -5, but with daylight savings.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Jay A. Kreibich]

On Mon, Aug 24, 2009 at 06:21:49PM -0400, Wilson, Ronald scratched on the wall:
> According to the documentation for CURRENT_TIMESTAMP, it should insert
> the current UTC date/time:  http://www.sqlite.org/lang_createtable.html.

> sqlite> select datetime('now', 'utc');
> 
> 2009-08-25 02:20:10

> Do I misunderstand something fundamental?


  Yes.  'now' is already in UTC, so there is no need to convert it.
  The syntax datetime('now', 'utc') is double-converting the time.
  
  The documentation for the 'utc' modifier is quite clear:

  "'utc' assumes that the string to its left is in the local timezone
  and adjusts that string to be in UTC. If the prior string is not in
  localtime, then the result of 'utc' is undefined."

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


-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP

[Pavel Ivanov]

> Do I misunderstand something fundamental?

According to http://www.sqlite.org/lang_datefunc.html datetime('now')
returns date and time already as UTC. If you add 'utc' modifier then
it makes datetime() think that it's your local time and convert it to
'utc' thus adding 4 hours (apparently you're in GMT -4 timezone).

Pavel

On Mon, Aug 24, 2009 at 6:21 PM, Wilson, Ronald wrote:
> According to the documentation for CURRENT_TIMESTAMP, it should insert
> the current UTC date/time:  http://www.sqlite.org/lang_createtable.html.
>
>
>
> However, there appears to be a mismatch with datetime('now', 'utc'):
>
>
>
> SQLite version 3.6.10
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> create table test(stamp DATETIME default CURRENT_TIMESTAMP);
>
> sqlite> insert into test (rowid) values(1);
>
> sqlite> insert into test values (CURRENT_TIMESTAMP);
>
> sqlite> select * from test;
>
> 2009-08-24 22:19:43
>
> 2009-08-24 22:19:55
>
> sqlite> select datetime('now', 'utc');
>
> 2009-08-25 02:20:10
>
> sqlite>
>
>
>
> If you ignore the minor time diff from my slow typing, there is a clear
> 4 hour difference between CURRENT_TIMESTAMP and datetime('now', 'utc').
> Do I misunderstand something fundamental?
>
>
>
> RW
>
>
>
> Ron Wilson, Engineering Project Lead
>
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[D. Richard Hipp]

On Aug 21, 2009, at 7:22 PM, Jean-Christophe Deschamps wrote:
>
> US "standard" for date is also completely awkward MM/DD/ as well  
> as
> most european (german for you, french for me) formats.

That is a "style" or "convention", not a "standard".  The standard is  
defined here: http://en.wikipedia.org/wiki/ISO_8601


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[Jean-Christophe Deschamps]

´¯¯¯
>(btw it's the standard datetime format in germany, not custom-designed 
>:-P)
`---

I see this as a confusion between a storage/computational format and 
human interface representation.

US "standard" for date is also completely awkward MM/DD/ as well as 
most european (german for you, french for me) formats. Nonetheless I 
bet I'm not the only one storing my (string) dates as /MM/DD inside 
databases in order to have a natural sort order with basic collating 
sequence (binary).

In the same way, Unix epoch runs as fast as Usain Bolt but is 
completely inadequate for human use (except for real programmers, of 
course).

Why not create view(s) to have your data accesible under your own 
computational-friendly format?  It would eat up some cycles but would 
make your nigths longer.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[Simon Slavin]

On 21 Aug 2009, at 11:37pm, Igor Tandetnik wrote:

> t-master  wrote:
>> the problem is, this db is created by another program and I don't
>> have the access to change the format
>
> What do you mean, don't have access? Can't you just run an UPDATE
> statement once, that changes all the fields to use a different format?

He may not be able to change a program which continues to add new data  
to the database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[Kit]

2009/8/21 t-master :
> Hi
> I have string in a table representing a DateTime.
> The format is 21.08.2009 00:25:00
> And I would like to compare it to "now"
> How can I do this?
> --
> View this message in context: 
> http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25085040.html
> Sent from the SQLite mailing list archive at Nabble.com.

I recomend to convert dateformat to julianday()
http://www.sqlite.org/lang_datefunc.html
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[t-master]

Igor Tandetnik wrote:
> 
> t-master  wrote:
>> I have string in a table representing a DateTime.
>> The format is 21.08.2009 00:25:00
> 
> I recommend you change the format. Yours is custom-designed to make your 
> life miserable.
> 
>> And I would like to compare it to "now"
> 
> select case when
> substr(T, 7, 4)||'-'||substr(T, 4, 2)||'-'||substr(T,1,2)||substr(T, 
> 11) > datetime('now')
> then 'future' else 'past' end
> from (select '21.08.2009 00:25:00' as T);
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Hi

the problem is, this db is created by another program and I don't have the
access to change the format (btw it's the standard datetime format in
germany, not custom-designed :-P)

-- 
View this message in context: 
http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25088287.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[Igor Tandetnik]

t-master  wrote:
> I have string in a table representing a DateTime.
> The format is 21.08.2009 00:25:00

I recommend you change the format. Yours is custom-designed to make your 
life miserable.

> And I would like to compare it to "now"

select case when
substr(T, 7, 4)||'-'||substr(T, 4, 2)||'-'||substr(T,1,2)||substr(T, 
11) > datetime('now')
then 'future' else 'past' end
from (select '21.08.2009 00:25:00' as T);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[Simon Slavin]

On 21 Aug 2009, at 7:25pm, t-master wrote:

> I have string in a table representing a DateTime.
> The format is 21.08.2009 00:25:00
> And I would like to compare it to "now"
> How can I do this?

If you need to know whether it's before or after instead of just  
equal, then you're going to need to hold the components of the date/ 
time in a more useful order: one in which a normal string sorting  
method will tell which is first.  In other words, the year must be  
first, not buried in the middle somewhere.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[John Stanton]

Sqlite does not have a DATETIME type.  It stores the decalred type but 
ignored it.  You can use it in your application.

Rael Bauer wrote:
> Hi,
>  
> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table 
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will the 
> declared default value be stored as a string or real value?
>  
> Also, more generally, how can I find out what storage type field values have 
> been stored in?
>  
> Thanks
> Rael Bauer
>
>
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   What you have above is data type DATE. DATETIME includes the TIME
component.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   String (the actual data storage type name is TEXT).

> Also, more generally, how can I find out what storage type field values
> have been stored in?

   Section 6.3 of Rick van der Lans's new "The SQL Guide to SQLite" covers
this topic. Use the 'typeof' command. Example:

SELECT typeof ('2009-07-27') as date;

   The returned result:

date

text

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[P Kishor]

On Mon, Jul 27, 2009 at 4:45 PM, Rael Bauer wrote:
> Hi,
>
> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table 
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will the 
> declared default value be stored as a string or real value?
>

string

> Also, more generally, how can I find out what storage type field values have 
> been stored in?
>

typeof()


> Thanks
> Rael Bauer
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[MikeW]

John Machin  writes:

>
> > "noon in Greenwich on November 24, 4714 BC" - 
> > presumably that's the beginning of time for Creationists ...
>
> You presume incorrectly; it's the start of Scaliger's 7980-year "Julian" 
> astronomical cycle. (http://en.wikipedia.org/wiki/Julian_day#History)
>
> You are possibly thinking of Archbishop Ussher's creation estimate of 23 
> October 4004 BC. (http://en.wikipedia.org/wiki/Dating_Creation).
>
> Cheers,
> John

Sorry, I meant to say _before_ the beginning of time ...   
   
Cheers,
MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[John Machin]

On 17/03/2009 1:00 AM, MikeW wrote:
> Timothy A. Sawyer  writes:
> 
[snip]

>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
> SNIP
> 
> "noon in Greenwich on November 24, 4714 BC" - 
> presumably that's the beginning of time for Creationists ...
> 

You presume incorrectly; it's the start of Scaliger's 7980-year "Julian" 
astronomical cycle. (http://en.wikipedia.org/wiki/Julian_day#History)

You are possibly thinking of Archbishop Ussher's creation estimate of 23 
October 4004 BC. (http://en.wikipedia.org/wiki/Dating_Creation).

Cheers,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[John Elrick]

MikeW wrote:
> Timothy A. Sawyer <tsaw...@...> writes:
>
>   
>> I stand corrected on the math
>>
>> Bottom line is that different applications use different baseline values for
>> 
> epoch (beginning of time)
>   
>> and you must know that baseline value
>> --Original Message--
>> From: Kees Nuyt
>> Sender: sqlite-users-boun...@...
>> To: sqlite-us...@...
>> ReplyTo: sqlite-us...@...
>> Sent: Mar 13, 2009 14:58
>> Subject: Re: [sqlite] datetime as integer
>>
>> Just a few corrections.
>>
>> 
> SNIP
>   
>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
>> 
> SNIP
>
> "noon in Greenwich on November 24, 4714 BC" - 
> presumably that's the beginning of time for Creationists ...
>   

That would be October 23, 4004 BCE (according the the Bishop of 
Ussher).  Noon, IIRC.  I would assume using Radiometric dating as a 
starting point would be a tad unwieldy for day to day usage.

http://en.wikipedia.org/wiki/Dating_Creation


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[P Kishor]

On Mon, Mar 16, 2009 at 9:00 AM, MikeW <mw_p...@yahoo.co.uk> wrote:
> Timothy A. Sawyer <tsaw...@...> writes:
>
>>
>> I stand corrected on the math
>>
>> Bottom line is that different applications use different baseline values for
> epoch (beginning of time)
>> and you must know that baseline value
>> --Original Message--
>> From: Kees Nuyt
>> Sender: sqlite-users-boun...@...
>> To: sqlite-us...@...
>> ReplyTo: sqlite-us...@...
>> Sent: Mar 13, 2009 14:58
>> Subject: Re: [sqlite] datetime as integer
>>
>> Just a few corrections.
>>
> SNIP
>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
> SNIP
>
> "noon in Greenwich on November 24, 4714 BC" -
> presumably that's the beginning of time for Creationists ...
>
> MikeW
> ;-)
>


guess it is better than using the apoplectic calendar.



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[MikeW]

Timothy A. Sawyer <tsaw...@...> writes:

> 
> I stand corrected on the math
> 
> Bottom line is that different applications use different baseline values for
epoch (beginning of time)
> and you must know that baseline value
> --Original Message--
> From: Kees Nuyt
> Sender: sqlite-users-boun...@...
> To: sqlite-us...@...
> ReplyTo: sqlite-us...@...
> Sent: Mar 13, 2009 14:58
> Subject: Re: [sqlite] datetime as integer
> 
> Just a few corrections.
>
SNIP
> For date calculations, SQLite prefers real values containing
> number of days since noon in Greenwich on November 24, 4714
> B.C., using the Proleptic Gregorian calendar:
SNIP

"noon in Greenwich on November 24, 4714 BC" - 
presumably that's the beginning of time for Creationists ...

MikeW
;-)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[Timothy A. Sawyer]

I stand corrected on the math

Bottom line is that different applications use different baseline values for 
epoch (beginning of time) and you must know that baseline value
--Original Message--
From: Kees Nuyt
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: sqlite-users@sqlite.org
Sent: Mar 13, 2009 14:58
Subject: Re: [sqlite] datetime as integer

Just a few corrections.

On Fri, 13 Mar 2009 13:48:46 +, "Timothy A. Sawyer"
<tsaw...@mybowlingdiary.com> wrote:

>To be able to do this you need the following data points:
>
>1. What is the integer representation of the date? 
>   Is it days or seconds from a certain date? 
>   Keep in mind that 1 day = 86400 seconds = 3600 minutes

Uhm, that's 1440 minutes.

>Most date representations are stored as seconds 
>since 1-1-1970 depending on the underlying OS
>
>2. Once you have figured out #1 then you need to perform
>   a function to convert that value to a SQLite date which
>   is stored as DD-MM- (or MM-DD-). 

SQLite accepts/recognizes date strings in the international
standard form: YYY-MM-DD HH:MM:SS (ISO-8601) and a few close
derivatives:
http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/ISO_8601

For date calculations, SQLite prefers real values containing
number of days since noon in Greenwich on November 24, 4714
B.C., using the Proleptic Gregorian calendar:
http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/Gregorian_calendar
http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Timothy A. Sawyer, CISSP
Managing Director
MBD Solutions
Phone: (603) 546-7132
Web: http://www.mybowlingdiary.com
Email: tsaw...@mybowlingdiary.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[Fazoogled]

You could just try this and see what happens:

char *ctime(const time_t *timer); 

The returned string is in the following format:

DDD MMM dd hh:mm:ss 

You may just be surprised or not!

Michael

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt
Sent: Friday, March 13, 2009 2:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] datetime as integer

Just a few corrections.

On Fri, 13 Mar 2009 13:48:46 +, "Timothy A. Sawyer"
<tsaw...@mybowlingdiary.com> wrote:

>To be able to do this you need the following data points:
>
>1. What is the integer representation of the date? 
>   Is it days or seconds from a certain date? 
>   Keep in mind that 1 day = 86400 seconds = 3600 minutes

Uhm, that's 1440 minutes.

>Most date representations are stored as seconds 
>since 1-1-1970 depending on the underlying OS
>
>2. Once you have figured out #1 then you need to perform
>   a function to convert that value to a SQLite date which
>   is stored as DD-MM- (or MM-DD-). 

SQLite accepts/recognizes date strings in the international
standard form: YYY-MM-DD HH:MM:SS (ISO-8601) and a few close
derivatives:
http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/ISO_8601

For date calculations, SQLite prefers real values containing
number of days since noon in Greenwich on November 24, 4714
B.C., using the Proleptic Gregorian calendar:
http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/Gregorian_calendar
http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[Simon Davies]

2009/3/13 John Machin :
> On 12/03/2009 12:21 AM, Nicolás Solá wrote:
>> Hi I’m using Trac software and it is implemented using SQLITE3. In Trac DB
>> schema there is a table called “milestone”. It has a field called “due” and
>> it means due date. The problem is that it uses an integer data type to store
>> the datum and I don’t know how to show it in a SELECT query as datetime. Can
>> anyone help me please?
>
> Help us to help you: tell us more about this date stored as integer.
> (1) the integer is the number of days since what date?
> or (2) some examples
> e.g. 2008-12-31 is represented as 123456
> 2009-01-01 is represented as 123457
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

We use Trac; it seems to me that the number is in unixepoch. Use
"DATE( due, 'unixepoch' )" to display the date in yyy-mm-dd format.

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[John Machin]

On 12/03/2009 12:21 AM, Nicolás Solá wrote:
> Hi I’m using Trac software and it is implemented using SQLITE3. In Trac DB
> schema there is a table called “milestone”. It has a field called “due” and
> it means due date. The problem is that it uses an integer data type to store
> the datum and I don’t know how to show it in a SELECT query as datetime. Can
> anyone help me please?

Help us to help you: tell us more about this date stored as integer.
(1) the integer is the number of days since what date?
or (2) some examples
e.g. 2008-12-31 is represented as 123456
2009-01-01 is represented as 123457
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Kees Nuyt]

On Sat, 28 Feb 2009 17:30:24 -0800 (PST), jonwood
 wrote:

>Thanks, but I'm not sure what this means. "SQLite date storage format and
>support" doesn't appear to be a specific term (at least, it didn't turn up
>anything specific on Google). 

I'm almost sure John Stanton had this in mind:

The number of days since noon in Greenwich on November 24,
4714 B.C. , as described in the return value of julianday().

http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar

Then, use the SQLite datetime functions to return any of a
few supported formats your application can cope with.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[John Stanton]

Look at the Sqlite sourcce code in the date function area and all, is 
revealed.

jonwood wrote:
> John Stanton-3 wrote:
>   
>> Use the Sqlite date storage format and support.  With that approach 
>> which is astronomivally correct you can deliver any date format or 
>> manipulwtion,  You may need some custom written functions.  to get week 
>> number according to national rules etc, but the method is sound.  It is 
>> also compatible with different date systems.
>>
>> 
>
> Thanks, but I'm not sure what this means. "SQLite date storage format and
> support" doesn't appear to be a specific term (at least, it didn't turn up
> anything specific on Google). Is there a link that would describe what
> you're referring to and how it'd help my situation?
>
> Thanks.
>
> Jonathan
>
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

>Storing dates with a two-digit year is...  The deja vu, the deja vu!
>Why, oh why re-create y2k?!

No one's talking about storing them that way. As far as printouts, I'm not
overly concerned about this client's reports being ambiguous in the year
2100.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22269622.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Jay A. Kreibich]

On Sat, Feb 28, 2009 at 12:28:33PM -0800, jonwood scratched on the wall:
> 
> 
> Derrell Lipman wrote:
> > 
> > http://sqlite.org/lang_datefunc.html
> > 
> 
> Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
> leading zeros, etc. Just as I pointed out in my original post.

  It should be pretty trivial to add those, however.  The strftime()
  function is right there in the code.  You could add a 'y' for
  two-digit years with about six lines of code.  Similar for 'h' - 12
  hour value, or 'A' - AM/PM indicator, or whatever.

  If you're not comfortable modifying the SQLite source for
  distribution reasons, or something like that, just bulk copy
  the strftime() function to your own external module or something and
  add/rewrite the bits you want.  Then you can link it in to any
  instance of SQLite you want.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Nicolas Williams]

On Sat, Feb 28, 2009 at 12:47:07PM -0800, jonwood wrote:
> > Database is for manipulating data. Your UI application is for presenting 
> > it nicely to the user. After all, you don't complain that SQLite, say, 
> > doesn't have functions for formatting numbers in user-friendly manner 
> > (e.g. 123,456.78).
> 
> So why does it have to be pre-formatted by storing it as text that I must
> parse and then reformat? If it just stored a date object in binary
> format--as a database should do--then I could easily format it to present to
> the user.

Why do you have to notice that SQLite3's date type is actually TEXT?
Use the functions provided and don't think of how they work (text
parsing and all that).  Just because a type isn't opaque doesn't mean
that you can't treat it as such...

> > You can store dates as doubles representing Julian dates, or as integers 
> > representing number of seconds since Unix epoch (aka time_t). Is this 
> > the kind of bindary format you are talking about?
> 
> Well, I don't know many CRT routines for working with Julian dates. time_t
> has support but they've kind of moved to a 64-bit version. I guess I could
> store it as a BLOB or store the year, month, day, hour, minute, and second
> in separate fields as well. But that doesn't seem like a very good approach
> to me.

No, no, just use SQLite3's date/time functions to convert to whatever
presentation form you want.  You can use them on the text form of dates
and on the integer form both.

Don't bother writing your own code to do these conversions when they are
already provided for you!

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Nicolas Williams]

On Sat, Feb 28, 2009 at 06:14:05PM -0800, jonwood wrote:
> >So I would just rock it and not worry about it too much. If you're really
> >that hard up on saving CPU cycles, they might be better gained elsewhere.
> 
> I just like to be efficient and thought I'd check in to see if I was missing
> anything. Looks like I'm not.

Storing dates with a two-digit year is...  The deja vu, the deja vu!
Why, oh why re-create y2k?!

Premature optimization and all that.

If INSERTs are rare and you're CPU-bound for SELECTs (because your DB
fits in memory) then just INSERT raw time and pre-computed presentation
forms.  If you're not CPU-bound then don't worry.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

>You may already be aware of this, but I didn't see it here in the
discussion
>so I thought I'd chime in. SQLite doesn't really provide a native datetime
>type. The data type documentation lays out what's going on in detail (link
>below), but I'm pretty certain that your datetime column is actually a TEXT
>type.

Yes, I did know there was something like that going on. And perhaps that's
the crux of the issue I'm having.

>So I would just rock it and not worry about it too much. If you're really
>that hard up on saving CPU cycles, they might be better gained elsewhere.

I just like to be efficient and thought I'd check in to see if I was missing
anything. Looks like I'm not.

Thanks,

Jonathan

-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22269245.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Billy Gray]

Hi Jonathon,
You may already be aware of this, but I didn't see it here in the discussion
so I thought I'd chime in. SQLite doesn't really provide a native datetime
type. The data type documentation lays out what's going on in detail (link
below), but I'm pretty certain that your datetime column is actually a TEXT
type.

http://sqlite.org/datatype3.html

If this poses an efficiency problem, others have suggested some good
work-arounds earlier. I suspect that for the general use-cases of SQLite,
storing date-time information in this way isn't really going have too bad of
an impact. And, as others have pointed out, you'll almost always have to do
some kind of mapping in your application from how the db stores date-times
to how your platform likes to do it (i.e. NSDate), and then you'll be
manipulating those objects at presentation time for the end-user.

So I would just rock it and not worry about it too much. If you're really
that hard up on saving CPU cycles, they might be better gained elsewhere.

Cheers,
Billy

On Sat, Feb 28, 2009 at 8:30 PM, jonwood  wrote:

>
>
> John Stanton-3 wrote:
> >
> > Use the Sqlite date storage format and support.  With that approach
> > which is astronomivally correct you can deliver any date format or
> > manipulwtion,  You may need some custom written functions.  to get week
> > number according to national rules etc, but the method is sound.  It is
> > also compatible with different date systems.
> >
>
> Thanks, but I'm not sure what this means. "SQLite date storage format and
> support" doesn't appear to be a specific term (at least, it didn't turn up
> anything specific on Google). Is there a link that would describe what
> you're referring to and how it'd help my situation?
>
> Thanks.
>
> Jonathan
>
>
> --
> View this message in context:
> http://www.nabble.com/DateTime-Objects-tp22264879p22268988.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

John Stanton-3 wrote:
> 
> Use the Sqlite date storage format and support.  With that approach 
> which is astronomivally correct you can deliver any date format or 
> manipulwtion,  You may need some custom written functions.  to get week 
> number according to national rules etc, but the method is sound.  It is 
> also compatible with different date systems.
> 

Thanks, but I'm not sure what this means. "SQLite date storage format and
support" doesn't appear to be a specific term (at least, it didn't turn up
anything specific on Google). Is there a link that would describe what
you're referring to and how it'd help my situation?

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268988.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[John Stanton]

Use the Sqlite date storage format and support.  With that approach 
which is astronomivally correct you can deliver any date format or 
manipulwtion,  You may need some custom written functions.  to get week 
number according to national rules etc, but the method is sound.  It is 
also compatible with different date systems.

jonwood wrote:
>>> Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
>>> leading zeros, etc. Just as I pointed out in my original post.
>>>   
>> Well, I would say you (or your users) live in the past. 
>> The rest of the world uses ISO-8601 ;)
>> http://www.cl.cam.ac.uk/~mgk25/iso-time.html
>> 
>
> Heh, well, I suppose I could tell the client that I can't match his current
> reports, or make the columns narrow enough to all fit on a single page and
> see if he still wants to pay me. But, with the economy what it is, I was
> toying with the idea of just giving him what he asked for.
>
>   
>> Pun aside, you can always deliver epoch (or something else
>> you find more convenient) to your application and let the
>> application do the formatting. 
>> SQL isn't meant for presentation anyway, it's for relational
>> storage.
>>
>> Example:
>> Compute the time since the unix epoch in seconds (like
>> strftime('%s','now') except this includes the fractional
>> part):
>>
>> SELECT (julianday('now') - 2440587.5)*86400.0; 
>> 
>
> Right. I think I'm getting the picture of my options. I already have a fair
> amount of code working that relies on the a DATETIME column and was hoping
> it would support a date format supported by C/C++ (like the way I read
> DateTime values with MS SQL and C# in .NET).
>
> I can work something out if these are my options though.
>
> Thanks.
>
> Jonathan
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

Doug-4 wrote:
> 
> I personally store my times as ints (__time64_t, or time_t).  When I read
> it
> back my app formats it however I want.  Simple :)
> 

I think that's the conclusion I'm coming to as well.

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268713.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

D. Richard Hipp wrote:
> 
> The date & time come out in an easily parseable format: -MM-DD  
> HH:MM:SS.  So you call:
> 
>  sscanf(zDateStr, "%d-%d-%d %d:%d:%d", , , , , , );
>  sprintf(zNewDate, "%d/%d/%d %d:%d%s", m, d, y%100, (H-1)%12+1, M,  
> H>=12 ? "pm" : "am");
> 
> Is that really so hard?
> 

I don't recall anyone saying anything about being hard. It's a
design/efficiency issue.

I may do what you suggest only because there is code in place already that
uses a DATETIME column. But in the future I think I'd recommend storing my
own integer date format, as others here have suggested.

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268698.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Doug]

>it just seems very inefficient to store a date as a string in a database.

I agree.  But why would you store it as a string??

I personally store my times as ints (__time64_t, or time_t).  When I read it
back my app formats it however I want.  Simple :)

Doug


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jonwood
> Sent: Saturday, February 28, 2009 5:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] DateTime Objects
> 
> 
> 
> Jonas Sandman wrote:
> >
> > So store your time as a 64-bit integer. Sqlite has support for that.
> >
> 
> Yeah, I can either do something like that or do parsing with existing
> column. I was just taking advantage of some of the properties provided
> by
> the DATETIME column and it just seems very inefficient to store a date
> as a
> string in a database.
> 
> Thanks.
> 
> Jonathan
> 
> 
> --
> View this message in context: http://www.nabble.com/DateTime-Objects-
> tp22264879p22268085.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[D. Richard Hipp]

On Feb 28, 2009, at 6:16 PM, jonwood wrote:
>
> Yeah, I can do parsing with existing column.

The date & time come out in an easily parseable format: -MM-DD  
HH:MM:SS.  So you call:

 sscanf(zDateStr, "%d-%d-%d %d:%d:%d", , , , , , );
 sprintf(zNewDate, "%d/%d/%d %d:%d%s", m, d, y%100, (H-1)%12+1, M,  
H>=12 ? "pm" : "am");

Is that really so hard?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Kees Nuyt]

On Sat, 28 Feb 2009 12:27:10 -0800 (PST), jonwood
 wrote:

>Derrell Lipman wrote:
>> 
>> http://sqlite.org/lang_datefunc.html
>> 
>
>Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
>leading zeros, etc. Just as I pointed out in my original post.

Well, I would say you (or your users) live in the past. 
The rest of the world uses ISO-8601 ;)
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Pun aside, you can always deliver epoch (or something else
you find more convenient) to your application and let the
application do the formatting. 
SQL isn't meant for presentation anyway, it's for relational
storage.

Example:
Compute the time since the unix epoch in seconds (like
strftime('%s','now') except this includes the fractional
part):

 SELECT (julianday('now') - 2440587.5)*86400.0; 


HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Jonas]

So store your time as a 64-bit integer. Sqlite has support for that.

On 28 feb 2009, at 21.47, jonwood  wrote:

>
>> Database is for manipulating data. Your UI application is for  
>> presenting
>> it nicely to the user. After all, you don't complain that SQLite,  
>> say,
>> doesn't have functions for formatting numbers in user-friendly manner
>> (e.g. 123,456.78).
>
> So why does it have to be pre-formatted by storing it as text that I  
> must
> parse and then reformat? If it just stored a date object in binary
> format--as a database should do--then I could easily format it to  
> present to
> the user.
>
>> You can store dates as doubles representing Julian dates, or as  
>> integers
>> representing number of seconds since Unix epoch (aka time_t). Is this
>> the kind of bindary format you are talking about?
>
> Well, I don't know many CRT routines for working with Julian dates.  
> time_t
> has support but they've kind of moved to a 64-bit version. I guess I  
> could
> store it as a BLOB or store the year, month, day, hour, minute, and  
> second
> in separate fields as well. But that doesn't seem like a very good  
> approach
> to me.
>
> -- 
> View this message in context: 
> http://www.nabble.com/DateTime-Objects-tp22264879p22266629.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Igor Tandetnik]

"jonwood"  wrote in
message news:22264879.p...@talk.nabble.com
> I have a SQLite table that contains a DATETIME value. However, the
> database does not appear to provide enough control over how that
> value is formatted. For example, I'd like a two-digit year, to
> eliminate leading zeros, and to show time using AM/PM format.

Database is for manipulating data. Your UI application is for presenting 
it nicely to the user. After all, you don't complain that SQLite, say, 
doesn't have functions for formatting numbers in user-friendly manner 
(e.g. 123,456.78).

> Is the ONLY way to accomplish this to manually parse the database
> value into separate values and then work with that? Is there no way
> to access these values in binary format without the overhead of
> convert to text and then back again?

You can store dates as doubles representing Julian dates, or as integers 
representing number of seconds since Unix epoch (aka time_t). Is this 
the kind of bindary format you are talking about?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

Derrell Lipman wrote:
> 
> http://sqlite.org/lang_datefunc.html
> 

Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
leading zeros, etc. Just as I pointed out in my original post.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22266413.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Derrell Lipman]

On Sat, Feb 28, 2009 at 1:47 PM, jonwood  wrote:

>
> Greetings,
>
> I have a SQLite table that contains a DATETIME value. However, the database
> does not appear to provide enough control over how that value is formatted.
> For example, I'd like a two-digit year, to eliminate leading zeros, and to
> show time using AM/PM format.
>

http://sqlite.org/lang_datefunc.html

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime issue with the time part

[Sebastien Robillard]

Eric Minbiole wrote:
> From what you describe, it seems that the compiler is performing 
> single-precision, rather than double-precision, math.  After a quick 
> Google search, I found a few posts indicating that Direct3D silently 
> switches the FPU from double to single precision math, presumably in 
> order to improve performance.
>
> While it seems nearly unconscionable that a graphics library would mess 
> with the FPU, the good news is that it appears you can override this 
> default behavior when creating a 3D device.  See "FpuPreserve" flag:
>
> http://msdn.microsoft.com/en-us/library/bb153282(VS.85).aspx
>
> Hope this helps,
>   Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

That was exactly the problem, using the FpuPreserve flag I have no 
problems at all. Thanks for your help.

- Sebastien R.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime issue with the time part

[Eric Minbiole]

> Once again, all of these problems doesn't happen before the creation of 
> the Direct3D device. Does anyone ever used SQLite successfully in a 
> full-screen 3D game ?

 From what you describe, it seems that the compiler is performing 
single-precision, rather than double-precision, math.  After a quick 
Google search, I found a few posts indicating that Direct3D silently 
switches the FPU from double to single precision math, presumably in 
order to improve performance.

While it seems nearly unconscionable that a graphics library would mess 
with the FPU, the good news is that it appears you can override this 
default behavior when creating a 3D device.  See "FpuPreserve" flag:

http://msdn.microsoft.com/en-us/library/bb153282(VS.85).aspx

Hope this helps,
  Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime issue with the time part

[Sebastien Robillard]

Eric Minbiole wrote:
> Sebastien Robillard wrote:
>   
>> Hi everyone,
>>   I have an issue with datetimes that doesn't return the "time" part 
>> correctly (always 00:00:00 or 18:00:00) when I use SQLite in my C++ 
>> code. Whenever I use datetime('now'), or current_timestamp, the time is 
>> not correct. However, it works correctly when using the sqlite3 program 
>> (sqlite-3_5_9.zip from download page). Right now I use the SQLite dll 
>> (sqlitedll-3_5_9.zip) in my code, but I also tried with the source code 
>> amalgamation with the same results 
>> 
>
> Your code looked correct, so I tried to reproduce the problem:  I 
> complied the sample code you provided using Visual Studio 2005 and the 
> v3.5.9 amalgamation.  Running under XP, I got the expected result:
>
> DATETIME('NOW') = 2008-07-14 18:29:49
>
> I assume that you are running under some flavor of Windows, since you 
> are using sqlitedll.  Perhaps start by adding some traces to SQLite 
> function "winCurrentTime()"?
>
> Good luck,
>   Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
What I omit to tell earlier is that I use SQLite to save some data for a 
game. After tracing my code a bit more, and after checking the 
winCurrentTime function as you proposed, I realized that the problem 
occurs only AFTER the creation of my Direct3D 9 device. Before that 
point, the dates works correctly. Once  my device is created, the 
numbers goes all wrong. I traced the winCurrentTime function and saw 
that there is errors in calculations. For exemple, this line
*prNow = (now + ft.dwLowDateTime)/8640.0 + 2305813.5;
gives me these numbers
*prNow = 148848.828125 + 2305813.5;
The result inside prNow is 2454662.25

Once again, all of these problems doesn't happen before the creation of 
the Direct3D device. Does anyone ever used SQLite successfully in a 
full-screen 3D game ?

Thanks for your help,

- Sebastien R.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datetime issue with the time part

[Eric Minbiole]

Sebastien Robillard wrote:
> Hi everyone,
>   I have an issue with datetimes that doesn't return the "time" part 
> correctly (always 00:00:00 or 18:00:00) when I use SQLite in my C++ 
> code. Whenever I use datetime('now'), or current_timestamp, the time is 
> not correct. However, it works correctly when using the sqlite3 program 
> (sqlite-3_5_9.zip from download page). Right now I use the SQLite dll 
> (sqlitedll-3_5_9.zip) in my code, but I also tried with the source code 
> amalgamation with the same results 

Your code looked correct, so I tried to reproduce the problem:  I 
complied the sample code you provided using Visual Studio 2005 and the 
v3.5.9 amalgamation.  Running under XP, I got the expected result:

DATETIME('NOW') = 2008-07-14 18:29:49

I assume that you are running under some flavor of Windows, since you 
are using sqlitedll.  Perhaps start by adding some traces to SQLite 
function "winCurrentTime()"?

Good luck,
  Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime bug

[P Kishor]

On 6/12/08, Shane Harrelson <[EMAIL PROTECTED]> wrote:
> I was able to reproduce this by setting by TZ to GMT +10:00.  It's a
>  floating point rounding issue in the julian date functions.   We're
>  investigating how to best correct it, but I don't have a "fix" for you now.

I can reproduce this bug on Mac OS X 10.5.3 with SQLite 3.5.9 without
having to tinker with TZ.


>
>
>  On 6/12/08, BareFeet <[EMAIL PROTECTED]> wrote:
>  >
>  > Hi Shane,
>  >
>  > >> This:  select datetime(julianday('2008-06-12','utc'),
>  > >> 'localtime');
>  > >>
>  > >> should give this:  2008-06-12 00:00:00
>  > >>
>  > >> but instead gives: 2008-06-11 24:00:00
>  >
>  > > Can you provide some details of your test setup?  What version of
>  > > SQLite?
>  > > What platform (compiler, O/S, processor, 32bit vs 64bit, etc.)?
>  >
>  > I'm using Mac OS X 10.5.3 on an iMac Intel dual 2.4GHz. I'm in
>  > Australia, near Sydney (GMT +10:00 I think).
>  >
>  > I get the same result above when using the command line tool of the
>  > built in SQLite version 3.4.0 or the latest binary version 3.5.9.
>  >
>  > FYI, this: select julianday('2008-06-12','utc');
>  > gives: 2454629.0833
>  >
>  > and this:  select datetime(2454629.0833, 'localtime');
>  > gives: 2008-06-11 24:00:00
>  >
>  > Thanks,
>  > Tom
>  > BareFeet
>  >
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime bug

[Shane Harrelson]

I was able to reproduce this by setting by TZ to GMT +10:00.  It's a
floating point rounding issue in the julian date functions.   We're
investigating how to best correct it, but I don't have a "fix" for you now.

On 6/12/08, BareFeet <[EMAIL PROTECTED]> wrote:
>
> Hi Shane,
>
> >> This:  select datetime(julianday('2008-06-12','utc'),
> >> 'localtime');
> >>
> >> should give this:  2008-06-12 00:00:00
> >>
> >> but instead gives: 2008-06-11 24:00:00
>
> > Can you provide some details of your test setup?  What version of
> > SQLite?
> > What platform (compiler, O/S, processor, 32bit vs 64bit, etc.)?
>
> I'm using Mac OS X 10.5.3 on an iMac Intel dual 2.4GHz. I'm in
> Australia, near Sydney (GMT +10:00 I think).
>
> I get the same result above when using the command line tool of the
> built in SQLite version 3.4.0 or the latest binary version 3.5.9.
>
> FYI, this: select julianday('2008-06-12','utc');
> gives: 2454629.0833
>
> and this:  select datetime(2454629.0833, 'localtime');
> gives: 2008-06-11 24:00:00
>
> Thanks,
> Tom
> BareFeet
>
> --
> SQLite GUI tools compared at:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime bug

[Dennis Cote]

BareFeet wrote:
> 
> I get the same result above when using the command line tool of the  
> built in SQLite version 3.4.0 or the latest binary version 3.5.9.
> 
> FYI, this: select julianday('2008-06-12','utc');
> gives: 2454629.0833
> 
> and this:  select datetime(2454629.0833, 'localtime');
> gives: 2008-06-11 24:00:00
> 

Tom,

There does seem to be a roundoff error in the calculation while adding 
the local timezone offset.

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select datetime(2454629.0833);
2008-06-11 14:00:00

The base value for the hour is 14, and you are in a timezone with a +10 
hour offset. It must be coming up with a sum that is slightly less than 
24 hours so that it doesn't increment the day. The value is rounded 
correctly when formatted for display. More fun with floating point 
representation. :-)

Dennis Cote



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime bug

[BareFeet]

Hi Shane,

>> This:  select datetime(julianday('2008-06-12','utc'),  
>> 'localtime');
>>
>> should give this:  2008-06-12 00:00:00
>>
>> but instead gives: 2008-06-11 24:00:00

> Can you provide some details of your test setup?  What version of  
> SQLite?
> What platform (compiler, O/S, processor, 32bit vs 64bit, etc.)?

I'm using Mac OS X 10.5.3 on an iMac Intel dual 2.4GHz. I'm in  
Australia, near Sydney (GMT +10:00 I think).

I get the same result above when using the command line tool of the  
built in SQLite version 3.4.0 or the latest binary version 3.5.9.

FYI, this: select julianday('2008-06-12','utc');
gives: 2454629.0833

and this:  select datetime(2454629.0833, 'localtime');
gives: 2008-06-11 24:00:00

Thanks,
Tom
BareFeet

--
SQLite GUI tools compared at:
http://www.tandb.com.au/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime bug

[Shane Harrelson]

Tom-

Can you provide some details of your test setup?  What version of SQLite?
What platform (compiler, O/S, processor, 32bit vs 64bit, etc.)?
I updated the date testscripts in CVS to add tests for you cases below, and
they worked correctly for version 3.5.9 of SQLite compiled with both GCC and
MSVC.

Thanks.
-Shane


On 6/11/08, BareFeet <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>
> Short question:
>
> This: select datetime(julianday('2008-06-12',
> 'utc'), 'localtime')
>
> should give this: 2008-06-12 00:00:00
>
> but instead gives this:   2008-06-11 24:00:00
>
> Is this a known bug?
>
>
> More detail:
>
> I am storing dates in julianday (real) format. When I store a date I
> store it as UTC offset and convert it to localtime when displaying it.
>
> So, using an example date, my formula (obviously simplified here)
> boils down to:
>
> select datetime(julianday('2008-06-12', 'utc'), 'localtime')
>
> or when I just want the date component:
>
> select date(julianday('2008-06-12', 'utc'), 'localtime')
>
> But I get unexpected answers:
>
> 2008-06-11 24:00:00   (I expected 2008-06-12 00:00:00)
>
> and:
>
> 2008-06-11(I expected 2008-06-12)
>
> This seems to me to be a bug, especially because it doesn't make sense
> for the datetime function to return a date plus 24 hours.
>
> It seems to be some rounding error in the date and datetime functions.
> I can manually "fix" it by:
>
> select datetime(julianday('2008-06-12', 'utc'), '0.1 seconds', ,
> 'localtime')
>
> or when I just want the date component:
>
> select date(julianday('2008-06-12', 'utc'), '0.1 seconds', 'localtime')
>
> Thanks,
> Tom
> BareFeet
>
> --
> SQLite GUI tools compared at:
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME data type

[Trey Mack]

Store it in '-mm-dd' format, or use the julian date that's suggested at:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who is
> older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME data type

[Igor Tandetnik]

"Yong Zhao" <[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who
> is older than certain date?
>
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
>
> select * from t1 where dob < '3/24/1950';

Store dates in -MM-DD format instead, then simple string comparison 
will also order dates correctly.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME data type

[Neville Franks]

Friday, February 29, 2008, 8:29:16 AM, you wrote:

YZ> It seems that sqlite3 does not support DATETIME data type.

YZ> If I have the following data in table t1, how do I select people who is
YZ> older than certain date?

YZ> create table t1(dob text, name text);
YZ> insert into t1('11/12/1930', 'Larry');
YZ> insert into t1('2/23/2003', 'Mary');

YZ> select * from t1 where dob < '3/24/1950';

Well I'm very new to SQLite but I think you need to use the formats
specified on "Date And Time Functions" Wiki page:
http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions 

Trying to compare dates in the format you have used would require
conversion to something sensible like -MM-DD

I am personally a big fan of the ISO-8601 format and use them
everywhere.

-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME data type

[John Stanton]

Try using the Sqlite date functions.

Yong Zhao wrote:
> It seems that sqlite3 does not support DATETIME data type.
> 
> If I have the following data in table t1, how do I select people who is
> older than certain date?
> 
> create table t1(dob text, name text);
> insert into t1('11/12/1930', 'Larry');
> insert into t1('2/23/2003', 'Mary');
> 
> select * from t1 where dob < '3/24/1950';
> 
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME data type

[Steven Fisher]

On 28-Feb-2008, at 1:29 PM, Yong Zhao wrote:

> It seems that sqlite3 does not support DATETIME data type.
>
> If I have the following data in table t1, how do I select people who  
> is
> older than certain date?

Use -MM-DD instead of M/D/Y. Available formats described here  
under Time Strings:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime column type, how-to

[Dmitry Bobrik]

Hello Uriel,

Tuesday, February 15, 2005, 2:19:07 AM, you wrote:

Unc> SQLiters:
Unc> What would be the best method for creating a Table with one column in the
Unc> format 03-Mar-2005 16:05:30?
Unc> i.e. dd-mmm-yy hh:mm:ss

You should store the datetime in SQL-Timestamp format, in case you
need a human-readable date. the format is:

-mm-dd hh:mm:ss

like

2005-02-15 11:55:54

it will be sorted correctly with ORDER BY

Unc> I will need to retrieve by date/time in proper order.  i.e. "select * from
Unc> 'table' ORDER BY datetime-field".


-- 
Best regards,
 Dmitrymailto:[EMAIL PROTECTED]

Re: [sqlite] datetime column type, how-to

[Jeff Thompson]

[EMAIL PROTECTED] wrote:
SQLiters:
What would be the best method for creating a Table with one column in the
format 03-Mar-2005 16:05:30?
i.e. dd-mmm-yy hh:mm:ss
 

I would store the date in UNIX timestamp format.
I will need to retrieve by date/time in proper order.  i.e. "select * from
'table' ORDER BY datetime-field".
The format you posted above won't sort correctly. Storing the timestamp 
in UNIX timestamp format would and you can format however you wish using 
the strftime sqlite function or your operating system's strftime function.

There's a date/time page at the sqlite wiki 
.

Hope that helps,
--
jthomps

Re: [sqlite] datetime modifier for localtime

[Derrell . Lipman]

Kurt Welgehausen <[EMAIL PROTECTED]> writes:

> If I remember right, localtime and gmtime were not implemented
> in 2.8.8.  You need to upgrade.

Ah!  Well that would certainly explain it.  Thanks!

Derrell

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]