Convert mysql DATETIME column to epoch seconds
Use MySQL’s UNIX_TIMESTAMP function
sql
PostgreSQL: Obtain rows from table from 5 minutes ago
Not sure why INTERVAL is not working: SELECT * FROM mytable WHERE “date” >= NOW() – INTERVAL ‘5 minutes’;
How to select all rows which have same value in some column
SELECT * FROM employees e1, employees e2 WHERE e1.phoneNumber = e2.phoneNumber AND e1.id != e2.id; Update : for better performance and faster query its good to add e1 before * SELECT e1.* FROM employees e1, employees e2 WHERE e1.phoneNumber = e2.phoneNumber AND e1.id != e2.id;
SQL Server: drop table cascade equivalent?
In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable ‘Generate script for dependent objects’. Then right click the table, script > drop to > new query window and it will generate it for you.
First day of the next month
First day of next month: sql-server 2012+ DATEADD(d, 1, EOMONTH(current_timestamp)) sql-server 2008 and older: DATEADD(m, DATEDIFF(m, -1, current_timestamp), 0)
Mapping values in SQL select?
You can use case expression: select id, name, case when category = ‘fruits’ then ‘Fruit Plant’ when category = ‘vegetables’ then ‘Vegetable Plant’ when category is null then ‘unknown’ end as category from Plant
linq to sql join on multiple columns using lambda
var query = context.ShoppingMalls .Join( context.Houses, s => new { s.CouncilCode, s.PostCode }, h => new { h.CouncilCode, h.PostCode }, (s, h) => s);
SQL – IF EXISTS UPDATE ELSE INSERT Syntax Error
INSERT INTO component_psar (tbl_id, row_nr, col_1, col_2, col_3, col_4, col_5, col_6, unit, add_info, fsar_lock) VALUES(‘2’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’, ‘1’, ‘N’) ON DUPLICATE KEY UPDATE col_1 = VALUES(col_1), col_2 = VALUES(col_2), col_3 = VALUES(col_3), col_4 = VALUES(col_4), col_5 = VALUES(col_5), col_6 = VALUES(col_6), unit = VALUES(unit), add_info = VALUES(add_info), fsar_lock = … Read more
SSIS Package not wanting to fetch metadata of temporary table
Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have … Read more
How can I correct the correlation names on this sql join?
Use table aliases for each reference to PoliticalFigures instead: SELECT Countries.Name AS Country, P.Name AS President, VP.Name AS VicePresident FROM Countries LEFT OUTER JOIN PoliticalFigures AS P ON Countries.President_Id = P.Id LEFT OUTER JOIN PoliticalFigures AS VP ON Countries.VicePresident_Id = VP.Id