CURRENT_TIMESTAMP records / displays incorrect value?

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

CURRENT_TIMESTAMP records / displays incorrect value?

Murray @ PlanetThoughtful-2
Hello All,

New to the list, so please forgive if this has been discussed previously.

I have a column defined with DEFAULT CURRENT_TIMESTAMP in an SQLite
3.2.7 db (on WinXP SP2, if that's important). I've noticed that the
value being stored in that column is being recorded / displayed
incorrectly. For example, it's currently 5:28am 22 Dec 2005 (in
Australia, if that causes confusion), and yet a record inserted at this
point contains "2005-12-21 19:28:54" when SELECTed back from the table.

Is this a known issue? I wondered if there was a time offset setting,
but haven't been able to find one in the documentation.

Any help appreciated!

Much warmth,

Murray
Reply | Threaded
Open this post in threaded view
|

Re: CURRENT_TIMESTAMP records / displays incorrect value?

rbundy

Refer http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions.
'localtime' has to be allowed for.

Regards.

rayB



|---------+---------------------------->
|         |           "Murray @        |
|         |           PlanetThoughtful"|
|         |           <lists           |
|         |                            |
|         |           22/12/2005 06:30 |
|         |           Please respond to|
|         |           sqlite-users     |
|         |                            |
|---------+---------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       [hidden email]                                                                      |
  |       cc:                                                                                                    |
  |       Subject:  [sqlite] CURRENT_TIMESTAMP records / displays incorrect value?                               |
  >--------------------------------------------------------------------------------------------------------------|




Hello All,

New to the list, so please forgive if this has been discussed previously.

I have a column defined with DEFAULT CURRENT_TIMESTAMP in an SQLite
3.2.7 db (on WinXP SP2, if that's important). I've noticed that the
value being stored in that column is being recorded / displayed
incorrectly. For example, it's currently 5:28am 22 Dec 2005 (in
Australia, if that causes confusion), and yet a record inserted at this
point contains "2005-12-21 19:28:54" when SELECTed back from the table.

Is this a known issue? I wondered if there was a time offset setting,
but haven't been able to find one in the documentation.

Any help appreciated!

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

****************************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: CURRENT_TIMESTAMP records / displays incorrect value?

Derrell Lipman
In reply to this post by Murray @ PlanetThoughtful-2
"Murray @ PlanetThoughtful" <[hidden email]> writes:

> I have a column defined with DEFAULT CURRENT_TIMESTAMP in an SQLite 3.2.7 db
> (on WinXP SP2, if that's important). I've noticed that the value being
> stored in that column is being recorded / displayed incorrectly. For
> example, it's currently 5:28am 22 Dec 2005 (in Australia, if that causes
> confusion), and yet a record inserted at this point contains "2005-12-21
> 19:28:54" when SELECTed back from the table.

Assuming that your column name is my_timestamp in table my_table, I expect
you'll get the results you're looking for by querying like this:

  SELECT datetime(my_timestamp, 'localtime') FROM my_table;

Derrell
Reply | Threaded
Open this post in threaded view
|

Re: CURRENT_TIMESTAMP records / displays incorrect value?

Murray @ PlanetThoughtful-2
[hidden email] wrote:

> "Murray @ PlanetThoughtful" <[hidden email]> writes:
>
>  
>> I have a column defined with DEFAULT CURRENT_TIMESTAMP in an SQLite 3.2.7 db
>> (on WinXP SP2, if that's important). I've noticed that the value being
>> stored in that column is being recorded / displayed incorrectly. For
>> example, it's currently 5:28am 22 Dec 2005 (in Australia, if that causes
>> confusion), and yet a record inserted at this point contains "2005-12-21
>> 19:28:54" when SELECTed back from the table.
>>    
>
> Assuming that your column name is my_timestamp in table my_table, I expect
> you'll get the results you're looking for by querying like this:
>
>   SELECT datetime(my_timestamp, 'localtime') FROM my_table;
>
> Derrell
>  

Thanks to Derrell and Ray -- I now have it working!

Much warmth,

Murray