[mapserver-dev] Adding a time index stamp to returned image.

Tue Jul 28 09:12:15 PDT 2015

Where does the timestamp literal, '2015-07-21 08:00:00', come from, and
can't you just use that directly?

On 07/24/2015 04:30 PM, Basques, Bob (CI-StPaul) wrote:
> my SQL looks like this:
> 
>  DATA "bbox from (
> SELECT
> row_number() over (order by accessed_at nulls last) as seq,
> client,
> extract('epoch' from (%time_idx1%::timestamp + INTERVAL '%min% minute' - accessed_at)::interval) as elapsed,
> id, accessed_at, bbox
>                         FROM
>                                 layer_metrics
>                         Where
>                                 accessed_at < (%time_idx1%::timestamp + INTERVAL '%min% minute')
> AND
>                                 accessed_at >= (%time_idx1%::timestamp + INTERVAL '%min% minute' - INTERVAL '10 minute')
> AND
> client NOT IN ('%172.17.17.97%','%172.17.17.151%')
> 
>                                          ) as subquery using unique id using srid=200068"
> 
> I tried a modified SQL like so:
> 
> SELECT
>                                 row_number() over (order by accessed_at nulls last) as seq,
>                                 client,
>                                 extract('epoch' from ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - accessed_at)::interval) as elapsed,
>                                 '2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' as tstamp,
>                                 id, accessed_at, to_char(accessed_at - INTERVAL '5 hours', 'HH24:MI:SS') as tstamp --,
>                                -- bbox
>                         FROM
>                                 layer_metrics
>                         Where
>                                 accessed_at < ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute')
>                         AND
>                                 accessed_at >= ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - INTERVAL '120 minute')
> 
> 
> to get the “tstamp" value in all rows:
> 
> 
> 1;"172.17.16.150";6860;"2015-07-21 08:25:00";2062502;"2015-07-21 06:30:40+00";"01:30:40"
> 2;"172.17.16.150";6860;"2015-07-21 08:25:00";2062503;"2015-07-21 06:30:40+00";"01:30:40"
> 3;"172.17.16.150";6860;"2015-07-21 08:25:00";2062504;"2015-07-21 06:30:40+00";"01:30:40"
> 4;"172.17.16.150";6860;"2015-07-21 08:25:00";2062505;"2015-07-21 06:30:40+00";"01:30:40"
> 5;"172.17.16.150";6857;"2015-07-21 08:25:00";2062506;"2015-07-21 06:30:43+00";"01:30:43"
> 6;"172.17.16.150";6857;"2015-07-21 08:25:00";2062507;"2015-07-21 06:30:43+00";"01:30:43"
> 7;"172.17.16.150";6857;"2015-07-21 08:25:00";2062508;"2015-07-21 06:30:43+00";"01:30:43"
> 8;"172.17.16.150";6857;"2015-07-21 08:25:00";2062509;"2015-07-21 06:30:43+00";"01:30:43"
> 9;"172.17.16.150";6842;"2015-07-21 08:25:00";2062510;"2015-07-21 06:30:58+00";"01:30:58"
> 10;"172.17.16.150";6842;"2015-07-21 08:25:00";2062511;"2015-07-21 06:30:58+00";"01:30:58”
> 
> Which outputs just peachy,  but for some reason a time object won’t come through via an expression for labelling.  I think my problem is I’m trying to do it all in one call.  I didn’t try a whole separate layer . . .anyway, any of the “tstamp” values will work for the label.
> 
> bobb
> 
> 
> 
> 
> 
> On Jul 24, 2015, at 4:08 PM, Lime, Steve D (MNIT) <Steve.Lime at state.mn.us<mailto:Steve.Lime at state.mn.us>> wrote:
> 
> Should be very doable – I like a challenge. You’re not saying much about how you were going about it. I created a quick test that drops the current time in the center of the map where the time is from the now() function in PostgreSQL. See:
> 
>   http://maps1.dnr.state.mn.us/cgi-bin/mapserv64?mode=map&map=/usr/local/mapserver/apps/test/timestamp/test.map
> 
> If you refresh it you’ll get the timestamp associated. Presumably you could format this to your liking using the various utilities in PostgreSQL. To do this I had to create a dummy table in my database that contained a single dummy column, and I added 1 row to it. That allowed me to get useful SQL generated via MapServer. The timestamp layer looks like:
> 
>  LAYER
>     NAME “timestamp”
>     TYPE POINT
>     STATUS DEFAULT
> 
>     TRANSFORM FALSE
>     UNITS PERCENTAGES
> 
>     CONNECTIONTYPE POSTGIS
>     CONNECTION “database of your choice”
>     DATA "the_geom FROM (SELECT ST_MakePoint(.50, .50) AS the_geom, NOW() AS tstamp, dummy_column FROM dummy_table) AS foo USING UNIQUE dummy_column USING SRID=-1"
>     LABELITEM tstamp
>     CLASS
>       LABEL
>         TYPE TRUETYPE
>         FONT “verdana-bold”
>         COLOR 0 0 0
>         SIZE 12
>         POSITION CC
>       END
>     END
>   END
> 
> To change the location of the point you’d edit the coordinates inside the ST_MakePoint() function. Hope it helps.
> 
> Steve
> 
> From: mapserver-dev-bounces at lists.osgeo.org<mailto:mapserver-dev-bounces at lists.osgeo.org> [mailto:mapserver-dev-bounces at lists.osgeo.org] On Behalf Of Basques, Bob (CI-StPaul)
> Sent: Tuesday, July 21, 2015 4:09 PM
> To: mapserver-dev at lists.osgeo.org<mailto:mapserver-dev at lists.osgeo.org>
> Subject: [mapserver-dev] Adding a time index stamp to returned image.
> 
> All,
> 
> I’m using a POSTGRES connection an trying to place a timestamp text chunk into the corner of a image from Postgres.
> 
> I’ve got a method for generating a set of images for a time-lapse movie (https://www.dropbox.com/s/7spkctc2mmtfmhc/out4.mp4?dl=0), but need to add a timestamp to each of the images upon output.  I’m not having much luck though outputting a seemingly simple item from Postgres.  Fro some reason TimeStamps are not completely understood by MapServer.  Maybe I need to cast the timestamp to a text string or something . . .
> 
> Thoughts??
> 
> Thanks
> 
> bobb
> 
> 
> 
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-dev
> 