current_timestamp redshift

Question

When I select current_timestamp from redshift, I am getting a list of values instead of one value.

Here is my SQL query

select current_timestamp 
from stg.table;

Does anybody know why I am getting a list of values instead a single value?

Answer 1

This is your query:

select current_timestamp from stg.table

This produces as many rows as there are in table stg.table (since that's the from clause), with a single column that always contains the current date/time on each row. On the other hand, if the table contains no row, the query returns no rows.

If you want just one row, use a scalar subquery without a from clause:

select current_timestamp as my_timestamp

Answer 2

You will receive a row for each row in stg.table. According to the RedShift docs you should be using GETDATE() or SYSDATE() instead. Perhaps you want, e.g.:

select GETDATE() as my_timestamp