Hitachi Vantara Pentaho Community Forums
Results 1 to 30 of 30

Thread: Wrong timestamp Information

  1. #1
    Join Date
    Mar 2010
    Posts
    181

    Default Wrong timestamp Information

    Get File Names step displays wrong time stamp information when I pull the files from a remote server. It adds 6 hours to the actual time stamp. Any idea ?


    Name:  linuxTimeStamp.jpg
Views: 69
Size:  30.5 KBName:  SpoonTimeStamp.jpg
Views: 64
Size:  14.2 KB

  2. #2
    Join Date
    Mar 2010
    Posts
    181

    Default

    More information....

    If I map the network drive (source folder for the files) then it shows the correct time stamp with seconds and milli-seconds. Whereas, If I use ftp URI, it adds 6 hrs to the original time stamp...Interesting !!

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Do you have a timezone difference by chance?
    ie. Is FTP using GMT while your workstation is using a different timezone?

  4. #4
    Join Date
    Mar 2010
    Posts
    181

    Default

    No both are same time zone.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    What time zone?

  6. #6
    Join Date
    Mar 2010
    Posts
    181

    Default

    Mst

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you said MDT, then the 6 hours would make sense... MDT is GMT-6
    I bet that if you did a full directory listing in a FTP client, you would see the other timezone in the FTP client as well.

  8. #8
    Join Date
    Mar 2010
    Posts
    181

    Default

    our sys admin verified that this is set to MDT. IS there a way this can be manipulated in Spoon and how ?

    Thanks for your quick responses. Appreciate your help.

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I'm willing to bet that the FTP service is giving you times in UTC, while your mapped folders are giving it to you in MDT.
    Depending on how you are using these timestamps, UTC may be more useful for you as it doesn't move around (there is not DT <-> ST switch in UTC)
    On the other hand, it may cause problems, since PDI takes time very seriously (look at time zone issues on the board - PDI will get very mad at you if you try to use a time that doesn't exist...)

    Can you try a listing using another FTP client (eg. Windows ftp.exe), to see if the FTP server is giving you UTC times?

  10. #10
    Join Date
    Mar 2010
    Posts
    181

    Default

    Yes I tried Filezila, WinScp and everything displays in MDT. I also remote logged in and got this

    $ date
    Thu Jun 9 14:06:44 MDT 2011

  11. #11
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    What FTP engine is running?
    ProFTPd has an option to turn off GMT Date display...

  12. #12
    Join Date
    Mar 2010
    Posts
    181

    Default

    We have openssh-server-4.3

  13. #13
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    That's an SSH / SCP / SFTP server, not FTP...
    Are you trying SFTP?
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  14. #14
    Join Date
    Mar 2010
    Posts
    181

    Default

    When I tried sftp no files got returned. However when I used FTP then it lists the file names with different time stamp.

  15. #15
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    The would indicate that there's an FTP server running there too... So your filezilla and WinSCP could be looking at a different port.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  16. #16
    Join Date
    Mar 2010
    Posts
    181

    Default

    but it is still referring to the same place holder to get the file names.

  17. #17
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Yup, but if the FTP engine is something like ProFTPD, then it could be converting the time to UTC before it's giving the timestamp out.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  18. #18
    Join Date
    Mar 2010
    Posts
    181

    Default

    OK. let me see if I can do anything about it. Thanks for your time and help. Appreciate it.

  19. #19
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Just a thought... Are the times actually being specified in UTC when you use the ftp:// VFS entry?

    Try connecting your Get File Name to a Select Values, and change the format of lastmodifieddate to: yyyy-MM-dd HH;mm:ss.SSS z
    It should then display the Timezone it thinks it has.

    If you are only looking for files that are newer than what you've already processed, then you're still because you can change the timezone of the date... but only if it is clearly coming in as UTC.

  20. #20
    Join Date
    Mar 2010
    Posts
    181

    Default

    It comes as MDT , but with 6 hours added.

    For ex actual time is 11:18:13 AM , but from Get File Names->Select Values -> "05:18:00 PM MDT"

  21. #21
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I can't manage to get onto IRC from here, so this will have to continue on the board...

    Were you able to find out what you FTP Server is?
    Also, if you enter the URL ftp://<server>/<directory> as it is in your Get Files Step into IE or Firefox, are the times correct then?
    With Get Filenames step, can you do sftp://<server>/<directory> ?

  22. #22
    Join Date
    Mar 2010
    Posts
    181

    Default

    Good questions.
    if I enter the URL ftp://<server>/<directory> IE or Firefox, the times are 6+ Hrs from MDT
    With Get Filenames step, I cannot do sftp://<server>/<directory> ? ONLY ftp protocol works

  23. #23
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by UcSam View Post
    Good questions.
    if I enter the URL ftp://<server>/<directory> IE or Firefox, the times are 6+ Hrs from MDT
    Indicates that your FTP server is changing the times, not PDI... Doubt we can fix it, since you may end up with invalid times (eg. 2011-03-13 02:30 ... Which PDI will not like)
    Quote Originally Posted by UcSam View Post
    With Get Filenames step, I cannot do sftp://<server>/<directory> ? ONLY ftp protocol works
    Perhaps SFTP isn't allowed by your OpenSSH install...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  24. #24
    Join Date
    Mar 2010
    Posts
    181

    Default

    FTP server is set to GMT time stamp. I was told to use SFTP uri and not FTP uri to get the local time zone time stamp. I get "Unable to get VFS File object for filename" if I use SFTP URI
    sftp://Unamewd@serverName/myFolder

    Our admin says SFTP service is enabled. I don't see any issue with my URI format. Is there any way I can test it ?

  25. #25
    Join Date
    Mar 2010
    Posts
    181

    Default

    I am also able to connect through SFTP client like winScp. Am I missing anything here ?

  26. #26
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Check things from http://wiki.pentaho.com/display/COM/...ing+Kettle+VFS

    That's about the best I can suggest.

    There's a way to put PDI into GMT, so maybe you could use that along with the GMT Timestamp to do your date checks...
    Last edited by gutlez; 06-13-2011 at 05:13 PM.

  27. #27
    Join Date
    Mar 2010
    Posts
    181

    Default

    This helped me a lot. I am able to move forward but Process files step fails now. It strips the password in "filename" variable which is an input from "Get file Names..."

    Unable to get VFS File object for filename 'sftp://ftpuser:@ct/myfolder/test/01AA_0001.XML' : Could not connect to SFTP server at "sftp://ftpuser@ct/".

    I never had problem with ftp uri..

  28. #28
    Join Date
    Mar 2010
    Posts
    181

    Default

    Never mind. I spoke too early. Everything works as expected. Thanks for your help.

  29. #29
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    What was the final solution?

  30. #30
    Join Date
    Mar 2010
    Posts
    181

    Default

    I set this variable vfs.sftp.StrictHostKeyChecking to no in the job.

    I posted one more question (separate thread) on password obfuscation issue. I am wondering if you have any suggestion.

    Again, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.