Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    18

    Unanswered: Extracting Hour From Timestamp

    Hi All,
    I am new to Postgresql and am enjoying it so far
    I have a table that I imported into postgresql from MS Access
    One of the fields is a time stamp that was imported in as a text field which was incorrect. I got around this by using the code
    Code:
    ALTER TABLE public."Table_1"
    ALTER COLUMN Col_1 TYPE timestamp without time zone USING (trim(Col_1)::timestamp without time zone);
    This had the desired effect and all the text was successfully changed to a time stamp for that column
    The next point that I wanted to tackle was to create/select a column which would just contain the hour from the time stamp.
    I tried
    Code:
    SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
    And

    Code:
    SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40')
    Both work perfectly ok giving me the hour of the time stamp when I statically put the dates in. However if I substitute the static variable for the column in my table as shown below,
    Code:
    SELECT 		EXTRACT(HOUR FROM TIMESTAMP AL1.Col_1)
    FROM 		public."Table_1" AL1;
    I get in the following error

    ERROR: syntax error at or near "AL1"
    LINE 1: EXTRACT(HOUR FROM TIMESTAMP AL1. Col_1)


    Any help would be greatly appreciated
    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I believe you're running into a case folding issue. Having upper/mixed case object names in postgresql isn't the best idea, as AL1 does not equal al1, and unless quoted, postgresql folds object names to lower case...

    As a work around, try wrapping AL1 with quotes.

    SELECT EXTRACT(HOUR FROM TIMESTAMP "AL1"."Col_1")
    FROM public."Table_1" "AL1";
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2013
    Posts
    18
    Hi,

    Thanks for the help but when i made this change i received the same error

    ERROR: syntax error at or near ""AL1""
    LINE 1: EXTRACT(HOUR FROM TIMESTAMP AL1. Col_1)


    I then took the aliases out completely and used just the table names themselves

    Code:
    SELECT 		EXTRACT(HOUR FROM TIMESTAMP public."Table_1".Col_1)
    FROM 		public."Table_1";
    This didn't work either
    Its very strange

    Once again thank you for your help

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by maccten View Post
    ...
    Code:
    SELECT 		EXTRACT(HOUR FROM TIMESTAMP AL1.Col_1)
    FROM 		public."Table_1" AL1;
    I get in the following error

    ERROR: syntax error at or near "AL1"
    LINE 1: EXTRACT(HOUR FROM TIMESTAMP AL1. Col_1)


    Any help would be greatly appreciated
    Thanks
    Try by removing a word "TIMESTAMP".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I saw the following descriptions in "PostgreSQL 8.2.0 Documentation".
    So, I thought that a word TIMESTAMP in your code might be for timestamp literal input and it might be unnecessary for timestamp column.

    8.5.1. Date/Time Input

    ...
    ...

    Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer
    to Section 4.1.2.5 for more information. SQL requires the following syntax
    type [ (p) ] ’value’
    where p in the optional precision specification is an integer corresponding to the number of fractional
    digits in the seconds field. Precision can be specified for time, timestamp, and interval types. The
    allowed values are mentioned above. If no precision is specified in a constant specification, it defaults to
    the precision of the literal value.

  6. #6
    Join Date
    Feb 2013
    Posts
    18
    Thank you, this appears to be the answer

Posting Permissions

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