Hitachi Vantara Pentaho Community Forums
Results 1 to 24 of 24

Thread: Adding the current date/time to the stream?

  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Default Adding the current date/time to the stream?

    What are my options for adding a field containing the current date and time to a row? My transformation updates a DB table, and I'd like to note when a row was last changed.

    I'm using a SQL2005 database, is there a way that I can pass the GETDATE() sql function through, similar to this raw sql "UPDATE myTable SET LastUpdated = GETDATE()"? Or, is there a way to use the Add Constants step to add the current date to the row?

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You can use the "Get System Info" step.

  3. #3
    Join Date
    Feb 2009
    Posts
    12

    Default

    Perfect, thanks.

    What's the difference between "system date (variable)" and "system date (fixed)"?
    I'm assuming the following:
    I'm updating 100 rows, one row at a time, if I use fixed each row's LastUpdated column will contain the same value. If I use variable, then each row will have a unique value based on the actual time of update.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Fixed is the start date/time of the transformation.
    Variable is the current time when the row passes.

  5. #5
    Join Date
    Mar 2012
    Posts
    15

    Default

    I realize this is an old thread, but is there any way to actually set a table field to a function (like setting a field = getdate()) instead of adding a field to the stream?

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    If you can't make it a default constraint, you will have to use an "Execute SQL script" step or something similar.
    So long, and thanks for all the fish.

  7. #7

    Default

    Hi,

    I am new here.
    Regarding this problem, if I want to add new field which is date (current date), do i just simply add the 'Get System Info' step and it will insert new column to my table?

    Thanks in advanced! ^^

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Don't wait for "Get System Info" to alter your table structure.
    You must add fields to a table definition, before you can address them in a "Table Output" step.
    So long, and thanks for all the fish.

  9. #9

    Default

    Yes, I do have created the new field in my table, but it seems that when I use "Get System Info", nothing happened.

    Or, I do it in wrong way. Any suggestion? Do I need any other step so I can get this "Get System Info" work?

    Thanks for your time! ^^

  10. #10

    Default

    I tried to use "Get System Info" step together with "Table Output", and when the etl process is running,
    it tried to insert into my target table (including the new field) but after that it aborted.

    What make it aborted? Is there something that I missed out?

    Thanks! ^^

  11. #11
    Join Date
    Mar 2013
    Posts
    3

    Default

    Buenos días,

    Yo tambien soy nueva aqui y tengo el mismo problema, tengo de entrada un fichero csv y salida una tabla sql, en la tabla le quiero añadir un campo con la fecha actual today, como debo hacerlo ya que no encuentro el get system info que comentaís aqui.

  12. #12
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    "Get System Info" step is located in the "Input" folder.
    So long, and thanks for all the fish.

  13. #13
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by Siti Khadijah View Post
    it tried to insert into my target table (including the new field) but after that it aborted.
    What make it aborted? Is there something that I missed out?
    My crystal ball seems to be broken.
    Don't you have more relevant information?

    Hint: What's in the log?
    So long, and thanks for all the fish.

  14. #14

    Default

    Quote Originally Posted by marabu View Post
    My crystal ball seems to be broken.
    Don't you have more relevant information?

    Hint: What's in the log?
    Sorry for not showing you the log earlier. So, here there are.

    Batch entry 0 INSERT INTO dim_goods_return_note_headers (olap_updated_at) VALUES ( '2013-03-11 10:03:16.895000 +08:00:00') was aborted. Call getNextException to see the cause.


    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1494)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:647)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:69)
    at java.lang.Thread.run(Thread.java:679)
    Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO dim_goods_return_note_headers (olap_updated_at) VALUES ( '2013-03-11 10:03:16.895000 +08:00:00') was aborted. Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2586)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1811)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1477)
    ... 3 more

    the new field 'olap_updated_at' is getting the date/time from 'Get System Info' but it was aborted.

    Thanks for your time!

  15. #15
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Since Kettle uses parameterized queries, there is nothing wrong with the value "Get System Info" returns.
    Turn off "Use batch update for inserts" to see the real error message.
    Most likely a constraint on this table is violated.
    So long, and thanks for all the fish.

  16. #16

    Default

    I am trying to add the value of current date time in my target table field update_date through "Get system info" step but it showing an error as "field{created in "get system info" step} is required and could not be found"

  17. #17
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Each step menu has action "Show input fields", so you can find out where you lost a field.
    So long, and thanks for all the fish.

  18. #18

    Default

    In input fied I am getting that field ..
    See I have one source CSV file having 25 columns and around 100 rows. My target table has 25+1 column with 26th column as update _date. I want to fill this column whenever a row is inserted in the table with the current date/time.

    Iam using "Get system info " step to get the date/time and have mapped it to the target table. But when running the query I am gettin error.

  19. #19
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Usually, timestamps are added in the database.
    Post the failing transformation if you need more help.
    So long, and thanks for all the fish.

  20. #20

    Default

    attached the file.
    my target table is in postgresql data base.
    Attached Files Attached Files

  21. #21
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    The "Get System Info" step adds its fields directly to the stream.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  22. #22

    Default

    Thanks :-)

  23. #23
    Join Date
    Mar 2017
    Posts
    1

    Default

    Quote Originally Posted by arpit016 View Post
    In input fied I am getting that field ..
    See I have one source CSV file having 25 columns and around 100 rows. My target table has 25+1 column with 26th column as update _date. I want to fill this column whenever a row is inserted in the table with the current date/time.

    Iam using "Get system info " step to get the date/time and have mapped it to the target table. But when running the query I am gettin error.

    Same problem!!Any solution??

  24. #24
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Easy: Same problem, same solution.
    So long, and thanks for all the fish.

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.