Thread: Timestamps in Views

Hi,
I  use a data formatting in views  (to_char(last_update, 'YYYY-MM-DD
HH:MM:SS') as last_update and noticed that the last update returned is
the creation date of the view or not the actual value of the
last_update.(last _update is a timestamp).

What do I need to do to display the actuall value?

Thanks
Alex

On Thursday 23 October 2003 09:07, Alex wrote:
> Hi,
> I  use a data formatting in views  (to_char(last_update, 'YYYY-MM-DD
> HH:MM:SS') as last_update and noticed that the last update returned is
> the creation date of the view or not the actual value of the
> last_update.(last _update is a timestamp).

It doesn't sound likely - could you provide the view definition and a couple
of lines of output?

--
  Richard Huxton
  Archonet Ltd

Richard,
have a look I also don't quite understand why it is happening.
The dates returned are actually not the creation date of the view but just a different date.
Thanks
Alex

# drop view view_joblog;
DROP VIEW
# CREATE VIEW view_joblog AS
#   SELECT log_id AS lid,job_id AS jid, job_type AS jtype, to_char(job_start, 'YYYY-MM-DD HH:MM:SS') AS
job_start,to_char(job_end,'YYYY-MM-DD HH:MM:SS') AS job_end,job_status AS jstat, 
#   log_file AS log_file, pid FROM job_log ORDER BY log_id;
CREATE VIEW
# select * from view_joblog where lid=530;
-[ RECORD 1 ]---------------------------------
lid       | 530
jid       | 198
jtype     | 21
job_start | 2003-10-07 10:10:28
job_end   | 2003-10-07 10:10:05
jstat     | 4
log_file  | clientfiles-21-20031007-103928.xml
pid       | 30724

# select log_id,job_id,job_type,job_start,job_end,job_status,log_file,pid  FROM job_log where log_id=530;
-[ RECORD 1 ]----------------------------------
log_id     | 530
job_id     | 198
job_type   | 21
job_start  | 2003-10-07 10:39:28.172914
job_end    | 2003-10-07 10:42:05.140341
job_status | 4
log_file   | clientfiles-21-20031007-103928.xml
pid        | 30724



Richard Huxton wrote:

>On Thursday 23 October 2003 09:07, Alex wrote:
>
>
>>Hi,
>>I  use a data formatting in views  (to_char(last_update, 'YYYY-MM-DD
>>HH:MM:SS') as last_update and noticed that the last update returned is
>>the creation date of the view or not the actual value of the
>>last_update.(last _update is a timestamp).
>>
>>
>
>It doesn't sound likely - could you provide the view definition and a couple
>of lines of output?
>
>
>

On Friday 24 October 2003 02:13, Alex wrote:
> Richard,
> have a look I also don't quite understand why it is happening.
> The dates returned are actually not the creation date of the view but just
> a different date. Thanks

> #   SELECT log_id AS lid,job_id AS jid, job_type AS jtype,
> to_char(job_start, 'YYYY-MM-DD HH:MM:SS') AS job_start,to_char(job_end,

> job_start | 2003-10-07 10:10:28

Ah! got it - you're using MM for month and minute. It'll be "m" or "i" or
something.

I've done the same, and looked at the code 100 times without noticing it.

--
  Richard Huxton
  Archonet Ltd