2nd question about 'localtime'

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

2nd question about 'localtime'

Murray @ PlanetThoughtful-2
Hi All,

I have a second question re: dates in SQLite and 'localtime'.

I have a table with a DEFAULT CURRENT_TIMESTAMP field.

I'm trying to retrieve a recordset of records that have been added to
that table today (but, of course, because the field stores UTC, the
value for the majority of those records are datetime values for yesterday).

In SQLSERVER2K I'm used to retrieving records for a given day by using
something like:

SELECT * FROM table WHERE datefield BETWEEN '2005-12-23' AND '2005-12-23';

I've experimented with a number of different SQL statements in SQLite to
achieve the same result, but nothing (so far) returns records I added
with a local date of today (which is '2005-12-23').

Can anyone help me understand how to achieve this in SQLite?

Many thanks and much warmth,

Murray
Reply | Threaded
Open this post in threaded view
|

Re: 2nd question about 'localtime'

Dennis Cote
Murray @ PlanetThoughtful wrote:

> Hi All,
>
> I have a second question re: dates in SQLite and 'localtime'.
>
> I have a table with a DEFAULT CURRENT_TIMESTAMP field.
>
> I'm trying to retrieve a recordset of records that have been added to
> that table today (but, of course, because the field stores UTC, the
> value for the majority of those records are datetime values for
> yesterday).
>
> In SQLSERVER2K I'm used to retrieving records for a given day by using
> something like:
>
> SELECT * FROM table WHERE datefield BETWEEN '2005-12-23' AND
> '2005-12-23';
>
> I've experimented with a number of different SQL statements in SQLite
> to achieve the same result, but nothing (so far) returns records I
> added with a local date of today (which is '2005-12-23').
>
> Can anyone help me understand how to achieve this in SQLite?
>
> Many thanks and much warmth,
>
> Murray
>
Murray,

Try the following:

select * from table where
    date(datefield) = date('now');

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: 2nd question about 'localtime'

rbundy
In reply to this post by Murray @ PlanetThoughtful-2

Does the column need to be loaded with UTC? Why not just insert the column
with localtime already applied? It would make the SELECT you require
trivial:

SELECT * FROM table WHERE datefield = '2005-12-23'.

Regards.

rayB



|---------+---------------------------->
|         |           "Murray @        |
|         |           PlanetThoughtful"|
|         |           <lists           |
|         |                            |
|         |           23/12/2005 02:35 |
|         |           Please respond to|
|         |           sqlite-users     |
|         |                            |
|---------+---------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       [hidden email]                                                                      |
  |       cc:                                                                                                    |
  |       Subject:  [sqlite] 2nd question about 'localtime'                                                      |
  >--------------------------------------------------------------------------------------------------------------|




Hi All,

I have a second question re: dates in SQLite and 'localtime'.

I have a table with a DEFAULT CURRENT_TIMESTAMP field.

I'm trying to retrieve a recordset of records that have been added to
that table today (but, of course, because the field stores UTC, the
value for the majority of those records are datetime values for yesterday).

In SQLSERVER2K I'm used to retrieving records for a given day by using
something like:

SELECT * FROM table WHERE datefield BETWEEN '2005-12-23' AND '2005-12-23';

I've experimented with a number of different SQL statements in SQLite to
achieve the same result, but nothing (so far) returns records I added
with a local date of today (which is '2005-12-23').

Can anyone help me understand how to achieve this in SQLite?

Many thanks and much warmth,

Murray




************** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING
*************
******************* Confidentiality and Privilege Notice
*******************

This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

****************************************************************************