Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    27

    Unanswered: SQLite - default for timestanp field

    Hi,

    is it possible to create a timestamp fild in a table which uses "now" as default if i enter nothing in an insert statement? How can i do this?
    Udate: my question was not complete, i dont want to insert UTC, i want to insert my local time

    roli
    Last edited by roli; 09-15-08 at 11:50.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does the SQLite manual give you any pointers?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2005
    Posts
    27
    Hi,

    in the documentation i found following: "my_field TIME DEFAULT CURRENT_TIME", which uses UTC. I haven't found anything about local time.

    roli

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    classically you would record date/times in UTC/Zulu Time/GMT
    then store the offset to local time.
    however the manual infers you can tinker around using local time
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2005
    Posts
    27
    Hi,
    Quote Originally Posted by healdem
    classically you would record date/times in UTC/Zulu Time/GMT
    then store the offset to local time.
    ok, but this meens to switch the delay twice a year because off summer & winter time.

    Quote Originally Posted by healdem
    however the manual infers you can tinker around using local time
    I read this before, but everything i tried ended with an error.

    roli

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest if local time is requried then you store the time as UTC/GMT and store the local time offset separately
    OR
    don't bother with Zulu/UTC time.

    without knowing what you application is doing its hard to say what is required.

    if your application is world wide, spanning several time zones then you may need to take account of local time variations. if its just a time marker its doesn't really matter.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2005
    Posts
    27
    Hi,

    i think i found a solution. I created the following trigger:

    Code:
    CREATE TRIGGER insert_localtime AFTER  INSERT ON my_tab
      BEGIN
        UPDATE my_tab SET start = TIME('NOW', 'LOCALTIME') WHERE rowid = new.rowid AND start = '';
      END
    The only thing that is a bit strange to me is that my trigger don't works if i use "AND start = NULL".
    Last edited by roli; 09-16-08 at 12:00. Reason: inserted code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •