Can date('now') fire a trigger automatically after a day passed?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Can date('now') fire a trigger automatically after a day passed?

csanyipal
Hi,

I have a column in a table where a value in a row is 'now'.
The 'now' value is in 'EndDate' column of that row.
I have also a trigger which fire up when in the 'EndDate' column a
Field was updated.

Because the 'now' value does changing every day in sense that every
one know that that yesterday's date is not equal with today's date I
am thinking about that that this could be used to fire up that trigger
automatically every day. But this does not work.

Then is there a mechanism which would update automatically such date
value - which is now the 'now' value in that 'EndDate' column Field
after a day passed?

This is needed in my application which calculates service time between
two dates, namely between StartDate and EndDate where EndDate has the
'now' value.

I hope I was clear what I mean.

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Can date('now') fire a trigger automatically after a day passed?

gbinfo
That would require some sort of active process or cron job. But that's
out of scope of SQLite. You will have to implement that on your own.

But if your EndDate is always "now", why having a true column at all? If
you do not want to take the current date from your environment, how
about something like this:

select *, datetime("now") as EndDate from mytable

No process, no cron job required. And your EndDate will always be "now".


> Hi,
>
> I have a column in a table where a value in a row is 'now'.
> The 'now' value is in 'EndDate' column of that row.
> I have also a trigger which fire up when in the 'EndDate' column a
> Field was updated.
>
> Because the 'now' value does changing every day in sense that every
> one know that that yesterday's date is not equal with today's date I
> am thinking about that that this could be used to fire up that trigger
> automatically every day. But this does not work.
>
> Then is there a mechanism which would update automatically such date
> value - which is now the 'now' value in that 'EndDate' column Field
> after a day passed?
>
> This is needed in my application which calculates service time between
> two dates, namely between StartDate and EndDate where EndDate has the
> 'now' value.
>
> I hope I was clear what I mean.
>
> --
> Best, Pali
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Can date('now') fire a trigger automatically after a day passed?

Hick Gunter
Maybe you are looking for

coalesce(EndDate,datetime('now'))

This will pretend that a NULL EndDate is always seen as the 'current datetime'. And you can find those records that need acting upon by the constraint EndDate IS NULL, run whenever your application feels like it needs to "do something".

Note that the notion of 'current datetime' remains constant only within a single call of sqlite3_step()

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von GB
Gesendet: Donnerstag, 09. August 2018 08:47
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Can date('now') fire a trigger automatically after a day passed?

That would require some sort of active process or cron job. But that's out of scope of SQLite. You will have to implement that on your own.

But if your EndDate is always "now", why having a true column at all? If you do not want to take the current date from your environment, how about something like this:

select *, datetime("now") as EndDate from mytable

No process, no cron job required. And your EndDate will always be "now".


> Hi,
>
> I have a column in a table where a value in a row is 'now'.
> The 'now' value is in 'EndDate' column of that row.
> I have also a trigger which fire up when in the 'EndDate' column a
> Field was updated.
>
> Because the 'now' value does changing every day in sense that every
> one know that that yesterday's date is not equal with today's date I
> am thinking about that that this could be used to fire up that trigger
> automatically every day. But this does not work.
>
> Then is there a mechanism which would update automatically such date
> value - which is now the 'now' value in that 'EndDate' column Field
> after a day passed?
>
> This is needed in my application which calculates service time between
> two dates, namely between StartDate and EndDate where EndDate has the
> 'now' value.
>
> I hope I was clear what I mean.
>
> --
> Best, Pali
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can date('now') fire a trigger automatically after a day passed?

Keith Medcalf
In reply to this post by csanyipal

Remember that date('now') is the UT1 date, not the local (wallclock) date.  To get the date 'now' for the timezone in which your computer thinks it is located you need to add the 'localtime' qualifier, as in date('now', 'localtime') ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Csányi Pál
>Sent: Wednesday, 8 August, 2018 23:59
>To: SQLite mailing list
>Subject: [sqlite] Can date('now') fire a trigger automatically after
>a day passed?
>
>Hi,
>
>I have a column in a table where a value in a row is 'now'.
>The 'now' value is in 'EndDate' column of that row.
>I have also a trigger which fire up when in the 'EndDate' column a
>Field was updated.
>
>Because the 'now' value does changing every day in sense that every
>one know that that yesterday's date is not equal with today's date I
>am thinking about that that this could be used to fire up that
>trigger
>automatically every day. But this does not work.
>
>Then is there a mechanism which would update automatically such date
>value - which is now the 'now' value in that 'EndDate' column Field
>after a day passed?
>
>This is needed in my application which calculates service time
>between
>two dates, namely between StartDate and EndDate where EndDate has the
>'now' value.
>
>I hope I was clear what I mean.
>
>--
>Best, Pali
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users