Firebird – How to Select and Show All Tickets from Last Calendar Year

date formatfirebirdselect

It seems SQL calls varies a lot, and cannot find how to do this in Firebird, where SQL dialect is 3.

There's a table called TICKETS. This table has INSERT_TIME which is TIMESTAMP. How do I select all the tickets which has insert time year = 2016?

Best Answer

Depending on your needs, you can use:

Using extract:

select *
from tickets
where extract(year from insert_time) = 2016

Using between:

select *
from tickets
where insert_time between timestamp'2016-01-01 00:00:00.0' and timestamp'2016-12-31 23:59:59.99999'

Depending on the volume of data, and the presence of an index on insert_time or not, the second may be preferable over the first.

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;

Mysql – How to get the current and next greater value in one select

Doing a JOIN is one thing you might need.

SELECT l.id, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id

I suppose the outer join is deliberate, and you want to be getting nulls. More on that later.

WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

You only want the r. row that has the lowest (MIN) time that is higher than the l.time. That is the place where you need subquerying.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)

Now to the nulls. If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.

You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :

SELECT id, time, 
    (SELECT MIN(time) FROM idtimes sub 
        WHERE sub.id = main.id AND sub.time > main.time) as nxttime
  FROM idtimes AS main

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Mysql – How to get the current and next greater value in one select

Related Question