Professional Web Applications Themes

timestamp data type - Microsoft SQL / MS SQL Server

If I have a timestamp column in my product table, how can I select only updated products from the last time? Should I campare: SELECT * FROM products WHERE Timestamp>oldTimeStamp oldTimestamp I remember from the last SELECT from the last week for example. Why use timestamp instead of datetime data type? Thank you, Simon...

  1. #1

    Default timestamp data type

    If I have a timestamp column in my product table, how can I select only
    updated products from the last time?

    Should I campare:

    SELECT * FROM products WHERE Timestamp>oldTimeStamp

    oldTimestamp I remember from the last SELECT from the last week for example.

    Why use timestamp instead of datetime data type?

    Thank you,
    Simon


    Simon Guest

  2. #2

    Default Re: timestamp data type

    Timestamp and datetime are different datatypes. Only datetime datatype
    stores date and time. The timestamp value is just a unique binary value.
    Timestamp is used for implementing custom locking solutions. That is, you
    read a row, and remember it's timestamp value. Do whatever you want with
    that row in your client application, and when you are writing the row back
    to the database, you check to see if that row's timestamp is the same as
    what you read before. If it is, you update the row, if it is different, then
    you know that the row has been updated by someone else, and you need to
    reconsile the changes. You can find more info on timestamp columns in SQL
    Server Books Online.
    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm


    "Simon" <si> wrote in message
    news:3f28f941$s5.net...
    If I have a timestamp column in my product table, how can I select only
    updated products from the last time?

    Should I campare:

    SELECT * FROM products WHERE Timestamp>oldTimeStamp

    oldTimestamp I remember from the last SELECT from the last week for example.

    Why use timestamp instead of datetime data type?

    Thank you,
    Simon



    Narayana Guest

  3. #3

    Default Re: timestamp data type

    Just an interesting thing that I wanted to share here about "timestamp". We
    had a similar argument about whether "timestamp" represented a date-time
    value. Here is what we found. Try this script:

    CREATE TABLE samp_test
    (
    colA INT,
    colB TIMESTAMP
    )
    GO
    INSERT INTO samp_test (colA) VALUES (1)
    INSERT INTO samp_test (colA) VALUES (2)
    INSERT INTO samp_test (colA) VALUES (3)
    INSERT INTO samp_test (colA) VALUES (4)
    GO
    SELECT colA, CAST(colB AS DATETIME) FROM samp_test

    This gives the output as follows (on my machine)
    colA
    ----------- -------------------------
    1 1900-01-01 00:00:42.003
    2 1900-01-01 00:00:42.007
    3 1900-01-01 00:00:42.010
    4 1900-01-01 00:00:42.013

    We can see that the "timestamp" value starts from 1900 and adds a few
    milliseconds each time. This led me to believe that "timestamp" is indeed a
    date-time value. Any thoughts on this??
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Dan Guzman" <net> wrote in message
    news:phx.gbl... 
    > only 
    > example. 
    >
    >[/ref]


    SriSamp Guest

  4. #4

    Default Re: timestamp data type

    Check this out :
    /****** :) best regards Jobi ******/
    set nocount on
    go

    CREATE TABLE dbo.T_Test (
    Sleutel int IDENTITY (1, 1) NOT NULL ,
    Ms_Ts timestamp NOT NULL ,
    Ms_Datetime datetime NOT NULL ,
    Ms_Datetime_Last_Used datetime NOT NULL ,
    Ms_Char char (10) NOT NULL
    )
    GO
    print 'Table reated'
    go
    ALTER TABLE dbo.T_Test WITH NOCHECK ADD
    CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,
    CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR
    Ms_Datetime_Last_Used,
    CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED
    (
    Sleutel
    )
    GO
    print 'Constraints added'
    go

    --drop trigger TrU_Ms_Datetime_Last_Used
    --go

    CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test
    FOR UPDATE

    AS

    if not UPDATE(Ms_Datetime_Last_Used)
    begin

    update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel =
    ( select sleutel from deleted )

    end
    go
    print 'Trigger added'



    insert into t_test (Ms_Char) values('a')
    go

    insert into t_test (Ms_Char) values('b')
    go
    Print 'Rows inserted'
    go


    select * from t_test
    go

    Print 'Update starts here...'
    go


    update t_test set ms_char = 'c' where sleutel = 1

    go

    Print 'Sleutel 1 updated ...'
    go

    select * from t_test
    go



    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
    , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
    from t_test
    order by sleutel

    go


    update t_test set ms_char = 'D' where sleutel = 2

    go

    Print 'Sleutel 2 updated ...'
    go

    select * from t_test
    go



    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
    , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
    from t_test
    order by sleutel
    go

    drop table dbo.t_test
    go

    print 'tabel dropped'
    go


    "Simon" <si> wrote in message
    news:3f28f941$s5.net... 
    example. 


    jobi Guest

  5. #5

    Default Re: timestamp data type

    Right. I agree with you. I tried:
    SELECT CAST (1.1 AS DATETIME)
    SELECT CAST (1.2 AS DATETIME)
    And it now gave differences at the time part.

    Was just interesting to see these datatypes map into datetime :-)
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "raydan" <nospamcom> wrote in message
    news:phx.gbl... 
    > We [/ref]
    indeed [/ref][/ref]
    database) [/ref][/ref]
    changed [/ref][/ref]
    may 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

  6. #6

    Default Re: timestamp data type

    Try it the other way around:
    select cast(getdate() as int)
    select cast(getdate() as float)
    select cast(getdate() as timestamp)
    select cast(getdate() as char(20))
    select cast(getdate() as binary)
    select cast(getdate() as bit)
    select cast(getdate() as money)
    select cast(getdate() as sql_variant)
    select cast(getdate() as image)

    Oops, the last one doesn't work.

    Just re-read my previous post and the "Tone" sounds a bit harsh.
    You don't seem too "ed off" but sorry about that anyway. :-)

    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 



    raydan Guest

Similar Threads

  1. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  2. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM
  3. Replies: 0
    Last Post: August 16th, 08:56 PM
  4. date/time/timestamp data
    By Kenjis Kaan in forum IBM DB2
    Replies: 2
    Last Post: July 30th, 07:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139