Python – Calculate sunrise and sunset times for a given GPS coordinate within PostgreSQL

gpspostgresqlpython

I want to classify timestamp data types in a PostgreSQL table with regards to whether they can be considered "at day" or "at night". In other words I want to be able to calculate sunrise and sunset times accurately, given a particular GPS position.

I know plpgsql and plpython.

Best Answer

Take a look at these links:

Calulating sunrise and sunset in Python;
Skyfield project (new incarnation of PyEphem)
PyEphem project;
astral project;

Related Solutions

Java – Get sunrise and sunset time

I understand you got the source from here. Looks like this is a very old source. Use the latest version available here.

Looks like you can achieve here by the following code.

Location location = new Location("39.9522222", "-75.1641667");
// Pass the time zone display here in the second parameter.
SunriseSunsetCalculator calculator = new SunriseSunsetCalculator(location, "America/New_York");

SQL: Subtracting 1 day from a timestamp date

Use the INTERVAL type to it. E.g:

--yesterday
SELECT NOW() - INTERVAL '1 DAY';

--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT 
    'yesterday'::TIMESTAMP, 
    'tomorrow'::TIMESTAMP, 
    'allballs'::TIME AS aka_midnight;

You can do the following then:

SELECT 
    org_id,
    count(accounts) AS COUNT,
    ((date_at) - INTERVAL '1 DAY') AS dateat
FROM 
    sourcetable
WHERE 
    date_at <= now() - INTERVAL '130 DAYS'
GROUP BY 
    org_id,
    dateat;

TIPS

Tip 1

You can append multiple operands. E.g.: how to get last day of current month?

SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';

Tip 2

You can also create an interval using make_interval function, useful when you need to create it at runtime (not using literals):

SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);

More info:

Date/Time Functions and Operators

datatype-datetime (Especial values).