Try something like:

    SELECT * from profiles WHERE to_timestamp(last_login) &lt; NOW() - INTERVAL &#39;30 days&#39; 

Quote from the manual:

&gt; A single-argument to_timestamp function is also available; it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

Unless I&#39;ve missed something, this should be pretty easy:

    SELECT * FROM profiles WHERE last_login &lt; NOW() - INTERVAL &#39;30 days&#39;;

How about

    SELECT * from profiles WHERE last_login &lt; VALUEOFUNIXTIME30DAYSAGO

or

    SELECT * from profiles WHERE last_login &lt; (extract(epoch from now())-2592000)

Have a look at this post:  

https://dba.stackexchange.com/questions/2796/how-do-i-get-the-current-unix-timestamp-from-postgresql

and this

http://www.epochconverter.com/




I have the following query:

    profiles = session.query(profile.name).filter(and_(profile.email == email, profile.password == password_hash))

How do I check if there is a row and how do I just return the first (should only be one if there is a match)?

Getting first row from sqlalchemy

I faced the problem that **one-to-one lazy loading** doesn&#39;t work in hibernate. I&#39;ve **already solved it**, but still **don&#39;t** properly **understand** what happens. 

My code (**lazy loading doesn&#39;t work here**, when I pull Person - Address is also fetched):

    @Entity
    public class Person{
    
      @Id
      @SequenceGenerator(name = &quot;person_sequence&quot;, sequenceName = &quot;sq_person&quot;)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;person_sequence&quot;)
      @Column(name = &quot;id&quot;)
      private long personID;
    
      @OneToOne(mappedBy=&quot;person&quot;, cascade=CascadeType.ALL, fetch = FetchType.LAZY)
      private Adress address;
      //.. getters, setters
    }
    
    @Entity
    public class Address {
      
      @Id
      @Column(name=&quot;id&quot;, unique=true, nullable=false)
      @GeneratedValue(generator=&quot;gen&quot;)
      @GenericGenerator(name=&quot;gen&quot;, strategy=&quot;foreign&quot;, parameters=@Parameter(name=&quot;property&quot;, value=&quot;person&quot;))
      private long personID;
    
      @PrimaryKeyJoinColumn
      @OneToOne
      private FileInfo person;
    }

**But**: if I add **`optional=false`** in OneToOne relationship, lazy loading **works fine**!

    @OneToOne(mappedBy=&quot;person&quot;, cascade=CascadeType.ALL, optional = false, fetch = FetchType.LAZY)
    private Adress address;

**Question/Entreaty:** please, explain to me how `optional=false` annotation helps to achieve lazy loading.

**P.S.** I&#39;ve read posts [post1][1] and [post2][2], and understand why simple OneToOne can&#39;t be lazy, but I still can&#39;t grasp `optional=false` magic.


  [1]: https://stackoverflow.com/questions/1444227/making-a-onetoone-relation-lazy
  [2]: https://community.jboss.org/wiki/SomeExplanationsOnLazyLoadingone-to-one

Hibernate: one-to-one lazy loading, optional = false

Now I&#39;ve found a lot of similar SO questions including an old one of mine, but what I&#39;m trying to do is get any record older than 30 days but my table field is unix_timestamp. All other examples seem to use DateTime fields or something. Tried some and couldn&#39;t get them to work.

This definitely doesn&#39;t work below. Also I don&#39;t want a date between a between date, I want all records after 30 days from a unix timestamp stored in the database. 
I&#39;m trying to prune inactive users.

simple examples.. doesn&#39;t work. 

    SELECT * from profiles WHERE last_login &lt; UNIX_TIMESTAMP(NOW(), INTERVAL 30 DAY)  

And tried this

    SELECT * from profiles WHERE UNIX_TIMESTAMP(last_login - INTERVAL 30 DAY) 

Not too strong at complex date queries. Any help is appreciate. 

SQL Get all records older than 30 days

Now I've found a lot of similar SO questions including an old one of mine, but what I'm trying to do is get any record older than 30 days but my table field is unix_timestamp. All other examples seem to use DateTime fields or something. Tried some and couldn't get them to work.
This definitely doesn't work below. Also I don't want a date between a between date, I want all records after 30 days from a unix timestamp stored in the database.
I'm trying to prune inactive users.
simple examples.. doesn't work.
<pre><code class="hljs language-sql">SELECT * from profiles WHERE last_login &#x3C; UNIX_TIMESTAMP(NOW(), INTERVAL 30 DAY) 
</code></pre>
And tried this
<pre><code class="hljs language-sql">SELECT * from profiles WHERE UNIX_TIMESTAMP(last_login - INTERVAL 30 DAY) 
</code></pre>
Not too strong at complex date queries. Any help is appreciate.

how can i query my sql server to only get the size of database? 

I used this : 

    use &quot;MY_DB&quot;
    exec sp_spaceused

I got this :

    database_name	database_size	unallocated space
    My_DB	        17899.13 MB	5309.39 MB

It returns me several column that i don&#39;t need, maybe there is a trick to select database_size column from this stored procedure ? 

I also tried this code :

    SELECT DB_NAME(database_id) AS DatabaseName,
           Name AS Logical_Name,
           Physical_Name,
           (size * 8) / 1024 SizeMB
    FROM sys.master_files
    WHERE DB_NAME(database_id) = &#39;MY_DB&#39;

It gives me this result:

    DatabaseName	Logical_Name	Physical_Name	                 SizeMB
    MY_DB	        MY_DB	        D:\MSSQL\Data\MY_DB.mdf	         10613
    MY_DB	        MY_DB_log	    D:\MSSQL\Data\MY_DB.ldf          7286

So i wrote this: 

    SELECT SUM(SizeMB)
    FROM (
        SELECT DB_NAME(database_id) AS DatabaseName,
               Name AS Logical_Name,
               Physical_Name,
               (size * 8) / 1024 SizeMB
        FROM sys.master_files
        WHERE DB_NAME(database_id) = &#39;MY_DB&#39;
    ) AS TEMP

I got: 1183

So it works but maybe there is a proper way to get this? 

Select SQL Server database size

What is difference between `!=` and `&lt;&gt;` operators in Sql Server? 

Since both are used as `not` operator. E.g :

    select * from TableName where ColName &lt;&gt; value

or 

    select * from TableName where ColName != value

returns same values (Rows).


What is difference between != and &lt;&gt; in sql server

Looking for elegant (or any) solution to convert columns to rows.

Here is an example: I have a table with the following schema:

    [ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

Here is what I want to get as the result:

    [ID] [EntityId] [IndicatorName] [IndicatorValue]

And the result values will be:

    1 1 &#39;Indicator1&#39; &#39;Value of Indicator 1 for entity 1&#39;
    2 1 &#39;Indicator2&#39; &#39;Value of Indicator 2 for entity 1&#39;
    3 1 &#39;Indicator3&#39; &#39;Value of Indicator 3 for entity 1&#39;
    4 2 &#39;Indicator1&#39; &#39;Value of Indicator 1 for entity 2&#39;

And so on..

Does this make sense? Do you have any suggestions on where to look and how to get it done in T-SQL? 

SQL Server : Columns to Rows

&gt; The ORDER BY clause is invalid in views, inline functions, derived
&gt; tables, subqueries, and common table expressions, unless TOP, OFFSET
&gt; or FOR XML is also specified.

I am getting the above said error while trying to execute the following query. Can anyone please have a look and tell me what am I doing wrong here?

	SELECT 
		* 
	FROM (
		SELECT 
			Stockmain.VRNOA, 
			item.description as item_description, 
			party.name as party_name, 
			stockmain.vrdate, 
			stockdetail.qty, 
			stockdetail.rate, 
			stockdetail.amount, 
			ROW_NUMBER() OVER (ORDER BY VRDATE) AS RowNum
		FROM StockMain 
		INNER JOIN StockDetail 
			ON StockMain.stid = StockDetail.stid 
		INNER JOIN party 
			ON party.party_id = stockmain.party_id 
		INNER JOIN item 
			ON item.item_id = stockdetail.item_id 
		WHERE stockmain.etype=&#39;purchase&#39; 
		ORDER BY VRDATE DESC
	) AS MyDerivedTable
	WHERE 
		MyDerivedTable.RowNum BETWEEN 1 and 5	

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

How can I get the SQL Server server and instance name of the current connection, using a T-SQL script?

How to get current instance name from T-SQL

I&#39;m using the following to try and insert a record into a postgresql database table, but it&#39;s not working. I don&#39;t get any errors, but there are no records in the table. Do I need a commit or something? I&#39;m using the postgresql database that was installed with the Bitnami djangostack install.

    import psycopg2
    
    try:
        conn = psycopg2.connect(&quot;dbname=&#39;djangostack&#39; user=&#39;bitnami&#39; host=&#39;localhost&#39; password=&#39;password&#39;&quot;)
    except:
        print &quot;Cannot connect to db&quot;
    
    cur = conn.cursor()
    
    try:
        cur.execute(&quot;&quot;&quot;insert into cnet values (&#39;r&#39;, &#39;s&#39;, &#39;e&#39;, &#39;c&#39;, &#39;w&#39;, &#39;s&#39;, &#39;i&#39;, &#39;d&#39;, &#39;t&#39;)&quot;&quot;&quot;)
    except:
        print &quot;Cannot insert&quot;



Python psycopg2 not inserting into postgresql table

I&#39;m trying to get a &#39;date&#39; type that corresponds to the first day of the current month.  Basically one of my tables stores a date, but I want it to always be the first of the month, so I&#39;m trying to create a trigger that will get now() and then replace the day with a 1.

Get first date of month in postgres

In one of my select statements I&#39;ve got the following error:

    ERROR:  failed to find conversion function from unknown to text
    ********** Error **********
    ERROR: failed to find conversion function from unknown to text
    SQL state: XX000

This was easy to fix using `cast`, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.

This one is OK:

    select &#39;text&#39;
    union all
    select &#39;text&#39;;

This will return error:

    with t as (select &#39;text&#39;)    
    select * from t
    union all
    select &#39;text&#39;

I know I can fix it easily:

    with t as (select &#39;text&#39;::text)    
    select * from t
    union all
    select &#39;text&#39;

Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?

PostgreSQL 9.1.9

The same behavior on PostgreSQL 9.2.4 ([SQL Fiddle][1])


  [1]: http://sqlfiddle.com/#!12/d41d8/1262

Failed to find conversion function from unknown to text

I have a simple table in PostgreSQL that has three columns:

 - id serial primary key
 - key varchar
 - value varchar

I have already seen this question here on SO: https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql but I&#39;m wondering just how to get the id if it exists, instead of updating. If the standard practice is to always either &quot;insert&quot; or &quot;update if exists&quot;, why is that? Is the cost of doing a SELECT (LIMIT 1) greater than doing an UPDATE?

I have the following code

    INSERT INTO tag
        (&quot;key&quot;, &quot;value&quot;)
        SELECT &#39;key1&#39;, &#39;value1&#39;
    WHERE
        NOT EXISTS (
            SELECT id,&quot;key&quot;,&quot;value&quot; FROM tag WHERE key = &#39;key1&#39; AND value = &#39;value1&#39;
        );

which works in the sense that it doesn&#39;t insert if exists, but I&#39;d like to get the id. Is there a &quot;RETURNING id&quot; clause or something similar that I could tap in there?

Insert if not exists, else return id in postgresql

With postgresql 9.3 I can `SELECT` specific fields of a JSON data type, but how do you modify them using `UPDATE`? I can&#39;t find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:

```sql
postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values (&#39;{&quot;a&quot;:1,&quot;b&quot;:2}&#39;);
INSERT 0 1
postgres=# select data-&gt;&#39;a&#39; from test where data-&gt;&gt;&#39;b&#39; = &#39;2&#39;;
 ?column?
----------
 1
(1 row)
postgres=# update test set data-&gt;&#39;a&#39; = to_json(5) where data-&gt;&gt;&#39;b&#39; = &#39;2&#39;;
ERROR:  syntax error at or near &quot;-&gt;&quot;
LINE 1: update test set data-&gt;&#39;a&#39; = to_json(5) where data-&gt;&gt;&#39;b&#39; = &#39;2...
```


Content Type	Original Author	Original Content on Stackoverflow
Question	Panama Jack	View Question on Stackoverflow
Solution 1 - Sql	Ihor Romanchenko	View Answer on Stackoverflow
Solution 2 - Sql	Jan Spurny	View Answer on Stackoverflow
Solution 3 - Sql	tanstaafl	View Answer on Stackoverflow

SQL Get all records older than 30 days

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Hibernate: one-to-one lazy loading, optional = false

Getting first row from sqlalchemy

Attributions