logo
down
shadow

Oracle - Combine timestamp with date and remove duplicates


Oracle - Combine timestamp with date and remove duplicates

By : anushree gupta
Date : November 21 2020, 04:01 AM
I hope this helps . You can convert your numeric (apparently) start/end time representations into fractions of days, or into intervals, and add those to your converted date. Presumably you actually want 240000 to really be midnight the following day, rather than one second before that, if you're doing calculations. So for example you could do:
code :
select
    date '1900-01-01'
      + floor("JDDate" / 1000) * interval '1' year
      + (mod("JDDate", 1000) -1) * interval '1' day
      + floor("START" / 10000) * interval '1' hour
      + floor(mod("START", 10000) / 100) * interval '1' minute
      + mod("START", 100) * interval '1' second
      as start_datetime,
    date '1900-01-01'
      + floor("JDDate" / 1000) * interval '1' year
      + (mod("JDDate", 1000) -1) * interval '1' day
      + case when "END" = 240000 then interval '1' day
             else floor("END" / 10000) * interval '1' hour
                + floor(mod("END", 10000) / 100) * interval '1' minute
                + mod("END", 100) * interval '1' second
        end
      as end_datetime
from ccd021
where "JDDate" > 118000;
select distinct ltrim(substr(nr, -6), '0') as staff_nr,
    date '1900-01-01'
    + ...
with cte (staff_nr, start_datetime, end_datetime) as (
  select distinct ltrim(substr(nr, -6), '0'),
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + floor("START" / 10000) * interval '1' hour
        + floor(mod("START", 10000) / 100) * interval '1' minute
        + mod("START", 100) * interval '1' second,
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + case when "END" = 240000 then interval '1' day
               else floor("END" / 10000) * interval '1' hour
                  + floor(mod("END", 10000) / 100) * interval '1' minute
                  + mod("END", 100) * interval '1' second
          end
  from ccd021
  where "JDDate" > 118000
)
select staff_nr,
  to_char(start_datetime, 'YYYY-MM-DD HH24:MI:SS') as start_datetime,
  to_char(end_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  end_datetime - start_datetime as diff_in_days,
  24 * (end_datetime - start_datetime) as diff_in_hours,
  cast(end_datetime as timestamp) - cast(start_datetime as timestamp) as diff_as_interval
from cte
where end_datetime != start_datetime;


Share : facebook icon twitter icon
Save duplicates with most recent date and remove others from oracle

Save duplicates with most recent date and remove others from oracle


By : Bui Minh Long
Date : March 29 2020, 07:55 AM
this will help Currently I have a situation where I want to delete duplicates from a table in oracle. The problem is I want to save the most recent row (according to date field) and delete other duplicates. For example here is a small snapshot of my table: , Try this:
code :
delete task 
 where (num, downtime) not in (
  select num, max(downtime) 
    from task
   group by num
)
How to write date / timestamp string to Date timestamp column in Oracle DB?

How to write date / timestamp string to Date timestamp column in Oracle DB?


By : vijay chaudhary
Date : March 29 2020, 07:55 AM
I hope this helps . You should use to_date, to_timestamp and/or date_format functions to do the transformation from stringified date/timestamp values to their corresponding type-aware ones.
code :
data.withColumn("real_date", date_format(...))
  .write
  .mode("overwrite")
  .jdbc(jdbcString, "tableName", prop)
How to remove duplicates from comma separated list by regex in Oracle but I don't want duplicates values?

How to remove duplicates from comma separated list by regex in Oracle but I don't want duplicates values?


By : user7864467
Date : March 29 2020, 07:55 AM
This might help you Try this, as per article http://www.dba-oracle.com/t_extract_comma_delimited_strings_oracle_sql.html:
code :
select distinct str from
(select regexp_substr ('ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234', '[^, ]+',1, rownum) str 
from dual 
connect by level <= regexp_count ('ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234', '[^, ]+')) v;
How to Remove Duplicates based on Timestamp | R

How to Remove Duplicates based on Timestamp | R


By : user3275722
Date : March 29 2020, 07:55 AM
should help you out I am trying to remove duplicates based on the timestamp. So which every ID number came in first would stay, while the older one would be removed. , Using dplyr
code :
library(dplyr)

df1 %>% 
  mutate(time = as.POSIXct(time, format="%m/%d/%Y %I:%M:%S %p", tz=Sys.timezone())) %>% 
  group_by(sample) %>% 
  arrange(time) %>% 
  filter(time == first(time)) %>%
  mutate(time = format(strptime(time, "%Y-%m-%d %H:%M:%S"), "%m/%d/%Y %I:%M:%S %p"))

#> # A tibble: 10 x 2
#>       sample time                  
#>        <dbl> <chr>                 
#>  1 101446089 10/04/2019 08:54:41 AM
#>  2 101446157 10/04/2019 07:55:10 AM
#>  3 101450202 10/04/2019 10:29:09 AM
#>  4 101458494 10/04/2019 09:41:40 AM
#>  5 101461751 10/04/2019 12:06:21 PM
#>  6 101463063 10/04/2019 10:18:38 AM
#>  7 101486260 10/04/2019 01:24:32 PM
#>  8 101495613 10/04/2019 03:53:41 PM
#>  9 101496189 10/04/2019 04:24:14 PM
#> 10 101496859 10/04/2019 04:57:02 PM
How do I combine date from one timestamp and time from another timestamp?

How do I combine date from one timestamp and time from another timestamp?


By : walenchie9ja
Date : March 29 2020, 07:55 AM
Hope that helps Assuming you're able to create these as java.util.Date objects, you can rely on their milleseconds-from-epoch to get this information:
Related Posts Related Posts :
  • SQL Set Value of a Column Using COUNT(*)
  • SQL GROUP BY Column Value
  • Is it true that all joins following a left join in a SQL query must also be left joins? Why or why not?
  • Need SQL JOIN statement assistance
  • Unexpected results with PATINDEX
  • Oracle 11 query to combine multiple rows into 1 row
  • How to compare 2 converted dates in sql
  • From a bit complex SQL statement to preparedStatement
  • How to count distinct logins
  • SQL Server - Operand type clash: numeric is incompatible with datetimeoffset
  • Splitting 24 hours into 96 equal 15 minute intervals in T-SQL
  • WHERE clause not hitting on LEFT JOIN
  • SQL Pivot String Data
  • Redshift - Find % as compared to total value
  • How to find count of N columns in a table
  • SQL statement giving no result
  • IN condition is not working when the data is like 'Test','Test' from a result query
  • SUM of COUNT with only certain variables
  • How to get all products with at least X reviews in SQL?
  • sql Group by columns to the same row without join
  • Check if an integer is within the next 5 increments in SQL, when the numbers can wrap
  • Case and When query give me both column filled
  • How to check JSON string on contains field AND like value?
  • Find uncovered periods without exploding each combination
  • How to compare date to format date on oracle
  • SQL Server : duplicate rows when joining tables and inserting new data?
  • Array to json and row to json in SQL Server 2016
  • Modifying SQL code to pick the Dropdown list instead of constant text
  • How to get max value of column values in a record ? (BigQuery)
  • Elasticsearch filter after aggregation
  • Teradata: varchar to timestamp 2
  • specific row number with all the fields
  • INSERT INTO SELECT query in SQL where primary key is used and multiple duplicates are in another table
  • SQL Query - Eliminate records
  • What is wrong with this SQL join query
  • Hybris Flexible search union query to fetch products
  • Stale Data Count
  • Adding new column of total_event
  • Query to Skip the first Delimiter and select the second Delimiter In SQL
  • Very slow (12+ hours) large table joins in postgres
  • LINQ query for specific group
  • SQL - return xpath for multiple XML results
  • Can't compile my trigger in Apex Oracle SQL/PL PLS-00103
  • Select 1 field from a group
  • Airflow + pandas read_sql_query() with commit
  • Get higest value of each relation
  • SQL - select limit column2 values for distinct column 1
  • Compilation failed, PLS-00103 pl/sql
  • SQL Query for all IDs where all rows with that ID match a value in another column
  • How to correctly translate sql-code in Delphi
  • How do I join 2 tables to allocate items?
  • PARTITION BY in CASE doesn't work with several AND statements
  • SQL. How to combine two records with the same ID into one line based on value's in a column
  • How to update tables from an external Access database with identical tables?
  • Trying to find records between two dates on one table and some information on another
  • Why does adding ORDER BY drastically speed up query?
  • SQL syntax explanation (CAST, INSERT, DATE)
  • My query with <> to / NOT IN () clause not returning any values?
  • Comparing 2 tables
  • How do I filter my table to show the result in Oracle?
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org