postgresql,postgresql-9.3 , Concatenate date and time fields and turn into datetime postgresql


Concatenate date and time fields and turn into datetime postgresql

Question:

Tag: postgresql,postgresql-9.3

I have a table with the date and time fields separated

Table1

data           hora            id
2015-01-01    11:40:06          1 
2015-01-01    15:40:06          2
2015-01-02    15:40:06          3 
2015-01-05    10:40:06          4 
2015-01-05    15:40:06          5
2015-01-06    08:23:00          6

Now I need to consult the id between 2015-01-01 12:00:00 12:00:00 and 2015-01-05 12:00:00, , should return the ids 2,3,4. I'm trying to convert and concatenate the date and time fields that are separated in a single datetime field in order to use the 'between' but I can not hit the syntax can someone give an example?


Answer:

It works!

SELECT
    *
FROM
    tableA
WHERE
    (dataemissao + hora) BETWEEN (date '2015-01-21' + time '14:00') 
     AND  (date '2015-01-21' + time '18:00')

Related:


JSONB: more than one row returned by a subquery used as an expression


sql,postgresql,postgresql-9.4,jsonb,set-returning-functions
I am (still) new to postgresql and jsonb. I am trying to select some records from a subquery and am stuck. My data column looks like this (jsonb): {"people": [{"age": "50", "name": "Bob"}], "another_key": "no"} {"people": [{"age": "73", "name": "Bob"}], "another_key": "yes"} And here is my query. I want to...

How to create a SELECT query FROM “TABLE1 AND TABLE2”


sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

Update enum column in Laravel migration using PostgreSQL


postgresql,laravel,laravel-5,laravel-migrations
According to this answer, I have to run a raw query if I want to update an enum in MySQL. But with PostgreSQL, I can't use this query, and enum type for PostgreSQL in Laravel seems strange. Is there any way to update enum in a migration for postgreSQL ?...

Avoid calling COUNT twice in CASE expression (PostgreSQL)


sql,postgresql
Inside a larger query, I have to COUNT a variable, then if it is larger than 1, have the count as a string otherwise an empty string: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' I'm afraid this is slow because I use COUNT() twice. Is there a better...

Translation of interval


postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?

How to get second row in PostgreSQL?


sql,postgresql
I have next table: COLUMN_NAME DATA_TYPE PK NULLABLE inc_id bigint YES NO dt_cr timestamp NO NO email varchar(255) NO YES email column is not unique, so I should use GROUP BY email. Question: How can I get from this table minimal date, next row after minimal date and email related...

How to group following rows by not unique value


sql,postgresql,greatest-n-per-group,window-functions,gaps-and-islands
I have data like this: table1 _____________ id way time 1 1 00:01 2 1 00:02 3 2 00:03 4 2 00:04 5 2 00:05 6 3 00:06 7 3 00:07 8 1 00:08 9 1 00:09 I would like to know in which time interval I was on which...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders


postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...

what is the SQL prepared stament for lo_import in postgreSQL


c++,sql,database,postgresql,odbc
In postgreSQL we have option to import the file into database using lo_import method, which returns a OID using that we can export the file from database to the filesystem. eg: describe test table name text, file_id oid insert into test values('arul_test',lo_import('/home/arul/test.txt')); prepared statement would be insert into test values(?,?);...

Postgres Index-only-scan: can we ignore the visibility map or avoid heap fetches?


postgresql,indexing
Sorry, lots of context before the actual question as we've throughly researched this and I wanted to give you full context. Some context: postgres index-only-scans rely on the visibility map (VM). If a page is not marked as not-fully-visible in the visibility map, postgres fetches that page to ensure the...

How to delete replication slot in postgres 9.4


postgresql,replication,postgresql-9.4
I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --------------+--------------+-----------+--------+----------+--------+------+--------------+-------------...

postgresql mathematical formula error


postgresql
Hi I am trying to use a mathematical function on each row in postgresql. But It gives me a error. My Query: Select stock_inventory_line.product_code AS Sku, COUNT(sale_order_line.name) AS Qty_Sold, stock_inventory_line.product_qty AS Current_Qty, (stock_inventory_line.product_qty / Qty_Sold) AS NOM From sale_order_line, product_product, product_template, product_category, stock_inventory_line WHERE sale_order_line.product_id = product_product.id AND product_product.product_tmpl_id =...

Error while trying to insert data using plpgsql


postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

Redirect if ActiveRecord::RecordNotUnique error exists


ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...

PostgreSQL: trigger to call function with parameters


postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...

Return integer value of age(date) function in Postgres


postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...

JPA NamedNativeQuery syntax error with Hibernate, PostgreSQL 9


java,hibernate,postgresql,jpa
I am having a rather odd problem. I have native query which runs perfectly when executed on sql server: SELECT date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', result.date_time) ORDER BY date_time),0) AS cum_amt FROM peoplecounting.result order BY date_time; However, using this query as native query in JPA results in...

How to order SQL query result on condition?


sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...

PostgreSQL conditional statement


sql,postgresql,if-statement
I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT. My unsuccessful approach looks as below: DO $do$ BEGIN IF SELECT count(*) FROM table1 < 1 THEN SELECT * FROM...

How to use Rails #update_attribute with array field?


ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord
Each question has an array of tags. schema.rb: create_table "questions", force: true do |t| t.text "tags", default: [], array: true How to atomically append to tags? How to prevent dups within the array? I tried question.update_attribute tags: tags << :ruby, but this doesn't work. Rails 4.17 and Postgres. EDIT: This...

Is there a better way to write this query involving a self select?


sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Retrieve updated rows in AFTER UPDATE trigger Postgresql


postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...

Formatting dates in PostgreSQL


sql,postgresql,select,date-formatting
I have a field which its format is date with time as: YYYY-MM-DD HH-MM-SS for example: 2000-08-12 00:00:00 I want to get just the date part and change its format to DD/MMM/YYYY for example the expected result of the previous example will be: 12/Aug/2000 The field definition is: Ddate timestamp...

need help specifying potentially reserved words as strings in postgres query


postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....

Postgresql Update JDBC


java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

Subtract two columns of different tables


sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

What happens with duplicates when inserting multiple rows?


sql,postgresql,exception,duplicates,upsert
I am running a python script that inserts a large amount of data into a Postgres database, I use a single query to perform multiple row inserts: INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc I was wondering what would happen if it hits a duplicate key for the insert....

Save a hex-string to PostgreSQL column character varying


postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...

How to customize the configuration file of the official PostgreSQL docker image?


postgresql,docker
I'm using the the postgres official image https://registry.hub.docker.com/_/postgres/. And now I'm trying to customize its configuration. For this purpose the command sed is used, e.g. to change the max_connections: sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf I tried two methods to apply this configuration. The first is by adding...

Syntax error while creating table in PostgreSQL 8.1


postgresql
I have a syntax error in this query: CREATE TABLE test (LIKE original_table INCLUDING INDEXES); ERROR : syntax error at or near "INDEXES" I am using PostgreSQL 8.1 version. Where is the issue in my query?...

Sqoop Export with Missing Data


sql,postgresql,shell,hadoop,sqoop
I am trying to use Sqoop to export data from HDFS into Postgresql. However, I receive an error partially through the export that it can't parse the input. I manually went into the file I was exporting and saw that this row had two columns missing. I have tried a...

PostgreSQL can't find column


java,postgresql
I create a database that contains 4 column (ID_PRODUIT,NOM_PRODUIT,QUANTITE, PRIX).I tried to make a methods that allow me to serach Product(my class)using a string key,however, the query failed to identify the "NOM_PRODUIT" column and it turn "NOM_PRODUIT" to "nom_produit" in the messsage error. Connection conn = DriverManager.getConnection(url, user, passwd); PreparedStatement...

How to check what constraint has been violated?


java,sql,postgresql,exception
I'm used to check if the user try to store a valid data with cathcing the ConsratinViolationException, like that: try { //persisitng to a db } catch (ConstraintViolationException e){ //Print message } I'm using PostgreSQL and now I'm under the isssue that the persisitng can violate more than one different...

load the data from file using multi threading


java,postgresql
I want to load the data into PostgreSQL table using java program. In the java program I want to use multi threading and I use the COPY command API CopyManager copyManager = ((PGConnection)conn).getCopyAPI(); FileReader fileReader = new FileReader(filename); copyManager.copyIn("COPY "+tblname+ " FROM STDIN DELIMITER ',' ", fileReader); I have divided...

postgres: using previous row value when current row value is null


postgresql
In my app I need to produce a data output (to be then converted to JSON) for the data in a postgres database (which is actually temperature data gathered by sensors). The data is stored in device_history_log as follows (for data between 11am and noon): row_id;deviceid;sensor_value_raw;last_update 104401;20865735;21.56;"2015-06-10 11:00:14" 104432;493417852;23.9;"2015-06-10 11:00:58"...

name of value returned from PostgreSQL function


postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...

postgresql complex group by in query


sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

Column does not Exist in postgresql?


sql,postgresql,postgresql-9.1
I am trying case inside select query and want to use that data column is generated by that case in same query. My query is : select order_id , order_item_id , sku ,merchant_payable as "Value Get" , case when name like 'Rise%' then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30) when name like...

How to install / use orafce package in postgresql 9.4?


postgresql,debian,orafce
I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce docker exec -i -t my_postgres bash apt-get install postgresql-9.4-orafce afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I...

Is there a better solution to join the same dataset to get the distinct count of a dimension used for aggregation?


sql,postgresql
I have to do an aggregation on certain measures, but i also have to say how much distinct values i have for a dimension, which i use for aggregation. I can solve the problem using two sub-selects, but performance is not the best. Is there any way to solve this...

Speed up Min/Max operation on postgres with index for IN operator query


postgresql,postgresql-9.3
I would like to optimize the following query in postgres SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?) with an index like CREATE INDEX my_index ON products (tag_id, shipping DESC); Unfortunately this one is only used when it's just one tag. Almost alwayst it is queried for a handful...

Postgres SQL constraint a character type


postgresql,constraints
I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL...

In PostgreSQL is it possible to join between table and function?


sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...

Subtract hours from the now() function


sql,postgresql,datetime,timezone,date-arithmetic
We have a machine running 24x7. Every day I report the number of pieces it produced per hour. In our case one working day means '2015-06-16 06:00:00' to '2015-06-17 06:00:00' for example. Here is my code: select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours, count (distinct t_el_eventlog.serialnumber) as count...

Connecting C++ and Postgresql


c++,postgresql,linker-error,libpqxx
I'm trying to connect C++ to Postgres. I recently installed libpqxx with Homebrew as follows: brew install libpqxx Then I made it with: make DatabaseTest on the file DatabaseTest.cpp. I ran the following simple program and got a strange error that I'm not sure what to do with... #include <iostream>...

How to insert and Update simultaneously to PostgreSQL with sqoop command


postgresql,hadoop,hive,sqoop
I am trying to insert into postgreSQL DB with sqoop command. sqoop export --connect jdbc:postgresql://10.11.12.13:1234/db --table table1 --username user1 --password pass1--export-dir /hivetables/table/ --fields-terminated-by '|' --lines-terminated-by '\n' -- --schema schema It is working fine if there is not primary key constrain. I want to insert new records and update old records...

plv8 disadvantages or limitations?


postgresql,plpgsql,plv8
I'm playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don't really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for...

Spring Boot - How to set the default schema for PostgreSQL?


java,spring,hibernate,postgresql
What I am currently doing in my application.properties file is: spring.datasource.url=jdbc:postgresql://localhost:5432/myDB?currentSchema=mySchema Isn't there another property for this? Since it looks hacky and according to a post (still finding the link sorry, will update later), it is only applicable to PostgreSQL 9.4....

group by is not working in postgreSQL


sql,postgresql,group-by
I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me. This is working: SELECT "id", "createdAt", "updatedAt" FROM "tables" AS "Table" WHERE "Table"."createdAt" BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' GROUP BY "id"; This is what...