#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Location
    Rennes, FRANCE
    Posts
    11
    Rep Power
    0

    timestamp postgresql


    I would like to make an easy query in my postgresql with timestamp data.
    For example i'd like to do

    INSERT into myTable ( myTimeStamp ) VALUES ( '2004/11/31 14:30' )

    The problem is that i don't know how to convert my date from string to timestamp
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Try:

    INSERT into myTable ( myTimeStamp ) VALUES ( '2004/11/31 14:30'::timestamp )

    You may also want to check out the PostgreSQL docs on type casting.

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Location
    Rennes, FRANCE
    Posts
    11
    Rep Power
    0
    I thank you for your answer,

    But your query doesn't work

    I searched into Postgres doc, but i didn't find.

    Have you got a real example for an insert of timestamp into postresql db please.

    thx

    CÚdric DELAMARRE

    Originally Posted by bcyde
    Try:

    INSERT into myTable ( myTimeStamp ) VALUES ( '2004/11/31 14:30'::timestamp )

    You may also want to check out the PostgreSQL docs on type casting.

    HTH,
    -b
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Strange does your DB schema actually have the table named myTable with a field called myTimestamp? Can you post your table layout? What error do you get?

    The reason I ask about the naming is because if you have mixed case names then you will need to quote the table name and the field name, as PostgreSQL defaults to lowercase for table/fields and that could be one source of the error.

    -b
    PostgreSQL, it's what's for dinner...
  8. #5
  9. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,156
    Rep Power
    24
    INSERT INTO myTable (myTimestamp) VALUES (to_timestamp('2004/11/31 14:30', 'YYYY/MM/DD HH:MI'));
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Location
    Rennes, FRANCE
    Posts
    11
    Rep Power
    0
    THX,
    it's working very nice like this


    Originally Posted by kurious
    INSERT INTO myTable (myTimestamp) VALUES (to_timestamp('2004/11/31 14:30', 'YYYY/MM/DD HH:MI'));
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    1
    Rep Power
    0

    update query for Datetime in postgres


    i just want to store datetime and retrieve it later.

    1. like in this post you have given the insert query. can i have update query? where my_id=4

    2. which approach is good for storing/retriveing the datetime in the postgres DB. please suggest the DB type for this
    Datetime
    TimeStamp
    NpgsqlTime
    i just have to store it and show it at later point of time.

    Thanks in advance

    kamal
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Please read the chapter "Date/Time input" in the manual. It explains everything you need to know:

    http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT

IMN logo majestic logo threadwatch logo seochat tools logo