<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">
my SQL looks like this:
<div class=""><br class="">
</div>
<div class="">
<div class="">
<div class=""><font face="Courier" class=""> DATA "bbox from (</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>SELECT</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>row_number() over (order by accessed_at nulls last) as seq,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>client,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>extract('epoch' from (%time_idx1%::timestamp + INTERVAL '%min% minute' - accessed_at)::interval) as elapsed,</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>id, accessed_at, bbox</font></div>
<div class=""><font face="Courier" class=""> FROM</font></div>
<div class=""><font face="Courier" class=""> layer_metrics</font></div>
<div class=""><font face="Courier" class=""> Where</font></div>
<div class=""><font face="Courier" class=""> <span class="Apple-tab-span" style="white-space:pre">
</span>accessed_at < (%time_idx1%::timestamp + INTERVAL '%min% minute')</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>AND</font></div>
<div class=""><font face="Courier" class=""> <span class="Apple-tab-span" style="white-space:pre">
</span>accessed_at >= (%time_idx1%::timestamp + INTERVAL '%min% minute' - INTERVAL '10 minute')</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>AND</font></div>
<div class=""><font face="Courier" class=""><span class="Apple-tab-span" style="white-space:pre"></span>client NOT IN ('%172.17.17.97%','%172.17.17.151%')</font></div>
<div class=""><font face="Courier" class=""><br class="">
</font></div>
<div class=""><font face="Courier" class=""> ) as subquery using unique id using srid=200068"</font></div>
</div>
<div class=""><br class="">
</div>
<div class="">I tried a modified SQL like so:</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="Courier" class="">SELECT</font></div>
<div class=""><font face="Courier" class=""> row_number() over (order by accessed_at nulls last) as seq,</font></div>
<div class=""><font face="Courier" class=""> client,</font></div>
<div class=""><font face="Courier" class=""> extract('epoch' from ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - accessed_at)::interval) as elapsed,</font></div>
<div class=""><font face="Courier" class=""> '2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' as tstamp,</font></div>
<div class=""><font face="Courier" class=""> id, accessed_at, to_char(accessed_at - INTERVAL '5 hours', 'HH24:MI:SS') as tstamp --, </font></div>
<div class=""><font face="Courier" class=""> -- bbox</font></div>
<div class=""><font face="Courier" class=""> FROM</font></div>
<div class=""><font face="Courier" class=""> layer_metrics</font></div>
<div class=""><font face="Courier" class=""> Where</font></div>
<div class=""><font face="Courier" class=""> accessed_at < ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute')</font></div>
<div class=""><font face="Courier" class=""> AND</font></div>
<div class=""><font face="Courier" class=""> accessed_at >= ('2015-07-21 08:00:00'::timestamp + INTERVAL '25 minute' - INTERVAL '120 minute')</font></div>
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">to get the “tstamp" value in all rows:</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">
<div class=""><font face="Courier" class="">1;"172.17.16.150";6860;"2015-07-21 08:25:00";2062502;"2015-07-21 06:30:40+00";"01:30:40"</font></div>
<div class=""><font face="Courier" class="">2;"172.17.16.150";6860;"<font color="#ff4013" class=""><b class="">2015-07-21 08:25:00</b></font>";2062503;"2015-07-21 06:30:40+00";"01:30:40"</font></div>
<div class=""><font face="Courier" class="">3;"172.17.16.150";6860;"2015-07-21 08:25:00";2062504;"2015-07-21 06:30:40+00";"01:30:40"</font></div>
<div class=""><font face="Courier" class="">4;"172.17.16.150";6860;"2015-07-21 08:25:00";2062505;"2015-07-21 06:30:40+00";"01:30:40"</font></div>
<div class=""><font face="Courier" class="">5;"172.17.16.150";6857;"2015-07-21 08:25:00";2062506;"2015-07-21 06:30:43+00";"01:30:43"</font></div>
<div class=""><font face="Courier" class="">6;"172.17.16.150";6857;"2015-07-21 08:25:00";2062507;"2015-07-21 06:30:43+00";"01:30:43"</font></div>
<div class=""><font face="Courier" class="">7;"172.17.16.150";6857;"2015-07-21 08:25:00";2062508;"2015-07-21 06:30:43+00";"01:30:43"</font></div>
<div class=""><font face="Courier" class="">8;"172.17.16.150";6857;"2015-07-21 08:25:00";2062509;"2015-07-21 06:30:43+00";"01:30:43"</font></div>
<div class=""><font face="Courier" class="">9;"172.17.16.150";6842;"2015-07-21 08:25:00";2062510;"2015-07-21 06:30:58+00";"01:30:58"</font></div>
<div class=""><font face="Courier" class="">10;"172.17.16.150";6842;"2015-07-21 08:25:00";2062511;"2015-07-21 06:30:58+00";"01:30:58”</font></div>
</div>
<div class=""><br class="">
</div>
<div class="">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.</div>
<div class=""><br class="">
</div>
<div class="">bobb</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div>
<blockquote type="cite" class="">
<div class="">On Jul 24, 2015, at 4:08 PM, Lime, Steve D (MNIT) <<a href="mailto:Steve.Lime@state.mn.us" class="">Steve.Lime@state.mn.us</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div class="WordSection1" style="page: WordSection1; font-family: ComicSansMS; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">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:<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> <span class="Apple-converted-space"> </span><a href="http://maps1.dnr.state.mn.us/cgi-bin/mapserv64?mode=map&map=/usr/local/mapserver/apps/test/timestamp/test.map" style="color: purple; text-decoration: underline;" class="">http://maps1.dnr.state.mn.us/cgi-bin/mapserv64?mode=map&map=/usr/local/mapserver/apps/test/timestamp/test.map</a><o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">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:<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> LAYER<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> NAME “timestamp”<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> TYPE POINT<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> STATUS DEFAULT<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> TRANSFORM FALSE<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> UNITS PERCENTAGES<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> CONNECTIONTYPE POSTGIS<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> CONNECTION “database of your choice”<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> 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"<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> LABELITEM tstamp<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> CLASS<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> LABEL<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> TYPE TRUETYPE<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> FONT “verdana-bold”<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> COLOR 0 0 0<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> SIZE 12<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> POSITION CC<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> END<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> END<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: 'Courier New'; color: rgb(31, 73, 125);" class=""> END<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">To change the location of the point you’d edit the coordinates inside the ST_MakePoint() function. Hope it helps.<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">Steve<o:p class=""></o:p></span></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div>
<div class="">
<div style="border-style: solid none none; border-top-color: rgb(225, 225, 225); border-top-width: 1pt; padding: 3pt 0in 0in;" class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<b class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class="">From:</span></b><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span class="Apple-converted-space"> </span><a href="mailto:mapserver-dev-bounces@lists.osgeo.org" class="">mapserver-dev-bounces@lists.osgeo.org</a>
[<a href="mailto:mapserver-dev-bounces@lists.osgeo.org" class="">mailto:mapserver-dev-bounces@lists.osgeo.org</a>]<span class="Apple-converted-space"> </span><b class="">On Behalf Of<span class="Apple-converted-space"> </span></b>Basques, Bob (CI-StPaul)<br class="">
<b class="">Sent:</b><span class="Apple-converted-space"> </span>Tuesday, July 21, 2015 4:09 PM<br class="">
<b class="">To:</b><span class="Apple-converted-space"> </span><a href="mailto:mapserver-dev@lists.osgeo.org" class="">mapserver-dev@lists.osgeo.org</a><br class="">
<b class="">Subject:</b><span class="Apple-converted-space"> </span>[mapserver-dev] Adding a time index stamp to returned image.<o:p class=""></o:p></span></div>
</div>
</div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
All,<span class="Apple-converted-space"> </span><o:p class=""></o:p></div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
I’m using a POSTGRES connection an trying to place a timestamp text chunk into the corner of a image from Postgres.<o:p class=""></o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
I’ve got a method for generating a set of images for a time-lapse movie (<a href="https://www.dropbox.com/s/7spkctc2mmtfmhc/out4.mp4?dl=0" style="color: purple; text-decoration: underline;" class="">https://www.dropbox.com/s/7spkctc2mmtfmhc/out4.mp4?dl=0</a>),
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 . . .<o:p class=""></o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
Thoughts??<o:p class=""></o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
Thanks<o:p class=""></o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
<o:p class=""> </o:p></div>
</div>
<div class="">
<div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">
bobb</div>
</div>
</div>
</div>
</blockquote>
</div>
<br class="">
</div>
</body>
</html>