Menu
  • HOME
  • TAGS

Groovy - timestamp from minutes

Tag: oracle,grails,groovy,timestamp

I have an array or times/values coming back to be in an array like:

[0, 60]

Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?

EDIT:* The date can be arbitrary, I just need the time portion for my purposes. EDIT:* For the above example I would need two timestamps maybe similar to this:

1/1/1970 12:00:00.000000 AM // for 0
1/1/1970 1:00:00.000000 AM // for 60

EDIT:

I can get the following so far:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"))
def date = new Date(0)

gives me:

Thu Jan 01 00:00:00 UTC 1970

So now How do I set the time based off of a value in minutes?

Best How To :

I assume you want to use the current day offset with the number of minutes given for your timestamp.

Since a new Date or Timestamp will be initialized to the current time and date, you can use that and override the minute field with the values from your array. Values 60 and greater will automatically carry over into hours, days, and so forth. Here's one way to do it:

def times = [0, 60]
def dates = times.collect { new Date().clearTime().copyWith(minute: it) }
def timestamps = dates.collect { new java.sql.Timestamp(it.time) }
println timestamps
// output: [2015-06-23 00:00:00.0, 2015-06-23 01:00:00.0]

The copyWith method was added in groovy 2.2.0. In older versions, you can use something like this:

def d = new Date().clearTime()
d.minutes = 60

Groovy - timestamp from minutes

oracle,grails,groovy,timestamp

I assume you want to use the current day offset with the number of minutes given for your timestamp. Since a new Date or Timestamp will be initialized to the current time and date, you can use that and override the minute field with the values from your array. Values...

How to use subquery result as the column name of another query

sql,oracle,plsql

Use your sub query as an inline table. Something like.... select item, item_type, .. decode(fore_column_name, 'foo', 1, 2) * 0.9 as finalforcast, decode(fore_column_name, 'foo', 1, 2) * 0.8 as newforcast from sales_data, ( select fore_column_name from forecast_history where ... ) inlineTable I'm assuming here that the value from the sub-query...

PLSQL - Error in associative array

oracle,plsql,associative-array

You have some unterminated append operations || on lines: Dbms_Output.Put_Line('Dropping TABLE '|| L_Key ||); And EXECUTE IMMEDIATE 'create table schema1.' ||l_key||' as select * from schema2.'||l_tbl(l_key)||; Get rid of the || at the end. Also the way you are using LOOP is incorrect. Refer example: while elem is not null...

How to pull the date in proper format from timestamp

oracle,timestamp

Try formatting your date to use a full 4 digit year using the to_char function and then do an export to excel. SELECT TO_CHAR(Trunc(assigned_date, 'IW'), 'MM-DD-YYYY') AS bonus_week Note: I am assuming you have an issue of 28 getting converted to 2028 instead of 1928. The solution above should fix...

Entity Framework code-first: querying a view with no primary key

sql,oracle,entity-framework,view,ef-code-first

It's not possible in Entity Framework to have Entities without primary key. Try to get a possible unique key from the views, combining columns, ... to create a unique primary key. If is not possible there is a workaround, if is only a queryable view, with out need to do...

Grails 3.0 Searchable plugin

maven,grails

The page you took the instructions from contains the following disclaimer: This portal is for Grails 1.x and 2.x plugins. Grails 3 plugins are available in Bintray https://bintray.com/grails/plugins There currenly is no version of this plugin for grails 3.x...

Log Grails Pre 3.0 startup time

grails

You could try: grails -Dgrails.script.profile=true Although it may require not using forked mode. Otherwise you will need to configure logging with time stamps for the org.codehaus.groovy.grails package....

Fill with zero to complete a defined number in sql [closed]

sql,oracle

You could use lpad, but if you're starting with a number you could use a 9-digit format model instead, and concatenate that onto your prefix: select '11111' || to_char(25, 'FM000000000') from dual; 11111000000025 The FM format modifier stops Oracle adding a space for a potential +/- sign indicator. SQL Fiddle...

Get only Oracle function return table's columns and their types

c#,oracle

For current login, use SELECT uta.attr_name, uta.attr_type_name || CASE WHEN uta.length IS NOT NULL THEN '('||uta.length||')' ELSE NULL END FROM user_coll_types uct JOIN user_type_attrs uta ON uta.type_name = uct.elem_type_name WHERE uct.type_name = 'COLLECTION_NAME' ORDER BY uta.attr_no; For the logged in user having access on types from another users: SELECT ata.attr_name,...

update a table from another table using oracle db

sql,oracle

Try this instead: UPDATE product SET provider_name = ( SELECT p.name FROM provider p WHERE p.provider_id = product.provider_id ); ...

Trying to access Oracle's Maven repository

oracle,maven,repository

From your settings.xml I can see that you use Artifactory. Why won't you define Oracle's repository as a remote in Artifactory? That will make the access much easier. Here's a simple guide on how to do so. And, of course, here's the official user guide on it. I am with...

grails 3.0.1 scaffolded view does not show domain relationship

grails,scaffolding

What you are seeing is this issue https://github.com/grails3-plugins/fields/issues/1 which when resolved will fix this problem.

SQL Developer does not connect with SID as defined in tnsnames.ora

oracle,oracle-sqldeveloper

The CLRExtProc entry in the tnsnames.ora is for external processes. That is not the database SID you use for normal client connections. The ORCL entry is defined to use servicename orcl. The service name and SID may or may not be the same. The database instance has a single SID,...

Optimizer using an index not present in the current schema

oracle,indexing,optimizer

You're connected as user ALLL, but you're querying a table in the HR schema: SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50; You stressed other schema in the question, but seem to have overlooked that the table you're querying is also in another schema. The employees...

sql script to find index's tablespace_name only

sql,database,oracle

SELECT DTA.* FROM DBA_TABLESPACES DTA, dba_indexes DI WHERE DI.TABLESPACE_NAME = DTA.TABLESPACE_NAME AND DI.OWNER ='USER' AND NOT EXISTS (SELECT 'x' FROM DBA_TABLES DTT WHERE DTT.TABLESPACE_NAME = DTA.TABLESPACE_NAME AND DTT.OWNER = DI.OWNER ); ...

Grails logging auto inject

grails,logback

add the @Slf4j annotation on your class. This local transform adds a logging ability to your program using LogBack logging. Every method call on a unbound variable named log will be mapped to a call to the logger. For this a log field will be inserted in the class. If...

Grails: Carry forward params on g:actionSubmit is clicked

grails,gsp

I don't think that you can pass params with actionSubmit in this way. You can use params attribute of g:form tag, like <g:form params="${params}"> ... </g:form> or <g:form params="[offset: params.offset, max: params.max]"> ... </g:form> ...

'ORA-00942: table or view does not exist' only when running within a Stored procedure

oracle,plsql

Sounds like an issue with select privileges granted via a role, rather than directly to the schema. See ORA-00942: table or view does not exist (works when a separate sql, but does nto work inside a oracle function).

Notice: Array to string conversion in “path of php file” on line 64

php,mysql,arrays,oracle

Curly brackets are your friend when inserting variables into double quoted strings: $main_query=oci_parse($connection,"INSERT INTO ROTTAN(NAME,ROLLNO) VALUES('{$array[$rs][0]}','{$array[$rs][1]}')"); ...

oracle sql error Case When Then Else

sql,oracle,oracle11g

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

Using MyBatis Update with foreach

sql,oracle,mybatis

try with open = "'" close = "'" and separator = "," and ${item} With these settings mybatis does not bind the variables but performs a String substitution. Be careful that string substittution is vulnerable to sql injection. If you face some more problems please post the updated sql query...

grails DataSource.groovy refer bean for decoding password

grails

I don't think this will be possible. The reason for this is the lifecycle (startup) of a Spring/Grails application requires that the DataSource be parsed while setting up the Spring application context. As such, making reference to a bean in the application context isn't going to be valid because the...

Oracle SQL - Returning the count from a delimited field

oracle

SQL Fiddle Oracle 11g R2 Schema Setup: CREATE TABLE TableAccount ( value ) AS SELECT 'P1:P2:P3' FROM DUAL UNION ALL SELECT 'P1' FROM DUAL UNION ALL SELECT 'P2:P3' FROM DUAL UNION ALL SELECT 'P1:P3' FROM DUAL UNION ALL SELECT 'P1:P4' FROM DUAL UNION ALL SELECT 'P5' FROM DUAL; Query 1:...

Extracting XML data from CLOB

sql,xml,oracle

Use xmltable. Data setup: create table myt( col1 clob ); insert into myt values('<ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" CurrencyId="43"/> </FoodItemDetails> <BillOption> <BillDetails TotalPrice="22222" BillOption="cash"/> </BillOption> <Authoritativeness/> </ServiceDetails>' ); commit; Query:...

Trigger to find next available inventory location

oracle,triggers,inventory

There are several ways to do it. You could add column AVAILABLE or OCCUPIED to first table and select data only from this table with where available = 'Y'. In this case you need also triggers for delete and for update of location_id on second table. Second option - when...

Can't obtain connection with the DB due to very long schema validation and connection reset afterwards

java,oracle,hibernate

Have you tried the solutions in the following question? Oracle 11g connection reset error One particular answer had to do with the following comment: I could get it resolved by adding this parameter to the Hotspot JVM: -Djava.security.egd=file:/dev/./urandom This issue does not affect windows, so it might be similar to...

Column ambiguously defined error with Oracle Merge statement

sql,oracle

You've selected b.itam_first_name and b.itam_last_name twice in the select list. Did you mean to? Should the last two have itrm instead of itam? SELECT A.itam_relevant_appl_code as ret, b.service_id, b.it_service, b.itam_user_id, b.itam_last_name, -- 1st occurrence b.itam_first_name, -- 1st occurrence b.itrm_user_id, b.itam_first_name, -- 2nd occurrence b.itam_last_name -- 2nd occurrence FROM ... If...

unable to resolve class org.apache.commons.net.ftp in grails

grails,apache-commons

In Grails you rarely add jar files to the project, you normally add dependencies. In your case you should add this line to the BuildConfig.groovy (section grails.project.dependency.resolution.plugins) compile 'commons-net:commons-net:3.3' ...

passing backbone collection to view

grails,backbone.js,handlebars

The reason you don't see any items is that the items aren't actually in the collection until after the view is rendered. Look at these two lines of code: var priceView = new PriceView({collection: prices}); prices.fetch(); The first line renders the view (since you're calling render from within initialize). However,...

Identifier is too long

sql,oracle

Use single quotes for string in PL SQL declare type app_realm_rec is record ( resource_filter varchar2(500), status varchar2(500), role varchar2(500) ); type app_realm_tab is table of app_realm_rec index by pls_integer; realm_tab app_realm_tab; begin realm_tab(1).resource_filter := '/secure/records*'; realm_tab(1).status := 'true'; realm_tab(1).role := 'ou=internal,ou=users,dc=chinastreet,dc=com;ou=external,ou=users,dc=chinastreet,dc=com'; realm_tab(2).resource_filter := '/secure/login'; realm_tab(2).status := 'false'; realm_tab(2).role :=...

Why I can't compare dates?

sql,oracle,oracle10g

First, your date comparison is too complicated. If h.time is an internal date format (which it should be), then just do: WHERE h.time BETWEEN DATE '2015-05-07' AND DATE '2015-06-07' Another very important issue with your query is that the WHERE clause is turning the LEFT JOIN into an INNER JOIN....

SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?

sql,oracle,select,substring

You can use a Regular Expression: regexp_substr('Hazel/Green==F123==Brown','(==F.+?==)') extracts '==F123==', now trim the =: ltrim(rtrim(regexp_substr('Hazel/Green==F123==Brown','(==F.+?==)'), '='), '=') If Oracle supported lookahead/lookbehind this would be easier... Edit: Base on @ErkanHaspulat's query you don't need LTRIM/RTRIM as you can specify to return only the first capture group (I always forget about that). But...

SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query

sql,oracle,sql-update

This code should be like this : sp_id = 'SP602' to sp_id = ''SP602'' and this '2' to ''2'' your final code should be like this Update RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK SET CHECK_SQL = 'select count(*) as CNT from O2_SDR_Header where id = (select max(id) from O2_SDR_Header where id > 2012000000000000 and sp_id...

like and regexp_like

sql,regex,oracle,oracle11g,regexp-like

Try this one: SELECT * FROM employee WHERE REGEXP_LIKE (fname, '^pr(*)'); Fiddle This one also seems to work as far as I can tell: SELECT * FROM employee WHERE REGEXP_LIKE (fname, '^pr.'); Or another one that works: SELECT * FROM employee WHERE regexp_like(fname,'^pr'); ...

Why does the date doesn't match with what I have inserted into the database?

sql,database,oracle

In the TO_DATE function you have to keep the format. The first parameter is the data and the second parameter is the format you are putting it. For example: to_date('29-Oct-09', 'DD-Mon-YY') to_date('10/29/09', 'MM/DD/YY') to_date('120109', 'MMDDYY') to_date('29-Oct-09', 'DD-Mon-YY HH:MI:SS') to_date('Oct/29/09', 'Mon/DD/YY HH:MI:SS') to_date('October.29.2009', 'Month.DD.YYYY HH:MI:SS') So if you put TO_DATE('05-06-2015','yyyy/mm/dd HH24:MI:SS')...

How to join 2 tables with select and count in single query

sql,oracle,left-join

With your sample data, you don't even need the Person table -- because you seem to have redundant table in the two tables. You should probably fix this, but: select pl.id, pl.name, count(*) from personline pl group by pl.id, pl.name; Your count is just counting all the rows from the...

Result from pipelined function, always will sorted as “written”, or not?

sql,oracle,plsql,oracle12c

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated pipelined-table-functions This means, it will start processing the rows before get fetched completely and that's why...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?

oracle,plsql

Some exceptions have names such as 'TOO_MANY_ROWS'. However, most of oracle exceptions do not have names. So if you want to trap any of them, you need to give them names. For your exception, you can do something like this: DECLARE .... NULL_STRING EXCEPTION; PRAGMA EXCEPTION_INIT(NULL_STRING, -06535); .... Begin .........

Dealing with nulls when selecting in MyBatis

java,oracle,mybatis

Try this way, <result column="DATE_TO" property="dateTo" jdbcType="DATE" javaType="java.util.Date"/> And also need to check and update your JDBC jar file.Check the compatibility of ojdbc.jar here (I think you should need at least ojdbc6.jar) . For additional checking, If you have declared in your mybatis configuration xml file like this <settings> <setting...

Cant delete in database because of constraints

c#,sql,asp.net,oracle

Best way to do it is by using a stored proceed rather than a sql statement in C# code. You are getting error because the referenced records are still present in referenced table and are using cmd.ExecuteReader(); rather than cmd.ExecuteNonQuery();. So you need to delete records for DBS2_MOVIE WHERE MOVIE_ID...

Find any character occur more than 4 times

sql,regex,oracle

SELECT regex_test_name FROM regex_test WHERE REGEXP_LIKE(regex_test_name, '([[:alpha:]])\1{3,9}') Inspired by dnoeth's answer, but since it catches the first character, specifying 3-9 subsequent repeats means 4-10 successive occurences in total....

Get unmatched records without using oracle minus except not in

oracle,plsql,inner-join,outer-join

The one option left with you is using NOT EXISTS SELECT t1.name FROM table1 t1 WHERE NOT EXISTS (SELECT 'X' FROM table2 t2 WHERE t2.name = t1.name); Update: Using Join with table_ as ( select t1.name t1_name, t2.name t2_name from table1 t1 left join table2 t2 on t1.name = t2.name)...

How to derive years of service for employees that have termed and returned several times

sql,oracle,oracle11g

As @PonderStibbons pointed out, this can be done quite simply by adding together the span of each hired period, and also adding the span of the between-hiring periods if the bridge value is 'Yes'. Open-ended hirings and different numbers of hirings can be handled by treating all null dates as...

Grails JAX-RS Calling a class in src/groovy giving error - Message: No signature of method: is applicable for argument types

grails,groovy,jax-rs

either use new ValidateToken.validate(... or make your validate method static. this is actually what the error is stating: No signature of method: static ....ValidateToken.validate() is applicable for argument types: () values: []` ...

Oracle 11g Insert Statement into Multiple Tables

sql,oracle,oracle11g,triggers,sql-insert

Based on your comment, I'm still not clear on what tool you are using to submit the statements to the database. It's quite possible that your query tool can only handle one statement at a time. And even if you batch up the 3 statements the way you did, those...

Exclude / ignore weekends in Oracle SQL

sql,oracle

Your query with small modification in case ... when excluding weekend days: select min(d), max(d), v from ( select d, v, sum( gc) over (partition by v order by d) g from ( select d, v, case when d-lag(d) over (partition by v order by d) = decode(trunc(d, 'iw') -...

Why does .Where() with a Func parameter executes the query?

c#,oracle,linq,entity-framework

There is a very important difference between Enumerable.Where and Queryable.Where: Enumerable.Where takes a Func<T, bool>. Queryable.Where takes an Expression. Your filter variable is not an Expression, it is a Func<T, bool>, therefore, the compiler uses Enumerable.Where. What happens then is that all rows of your FOO table are transferred to...

SQL*Loader Control File Custom Date Format

oracle,toad,sql-loader

Try with below code time date 'YYYY-MM-DD"T"HH24MISS' ...

Error when using angular with Grails

angularjs,grails

The error that you get says: Failed to instantiate module myApp due to: {1} Which basically means angular could not create an instance of myApp. You might want at a minimum level to have this code: var myApp = angular.module('myApp',[]); function MyCtrl($scope) { } and in your HTML: <html ng-app="myApp">...