public async Task Timestamp_read(LocalDateTime localDateTime, string s)
{
await using var conn = await OpenConnectionAsync();
await using var cmd = new NpgsqlCommand($"SELECT '{s}'::timestamp without time zone", conn);
await using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("timestamp without time zone"));
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(Instant)));
Assert.That(reader[0], Is.EqualTo(localDateTime.InUtc().ToInstant()));
Assert.That(reader.GetFieldValue <Instant>(0), Is.EqualTo(localDateTime.InUtc().ToInstant()));
Assert.That(reader.GetFieldValue <LocalDateTime>(0), Is.EqualTo(localDateTime));
Assert.That(reader.GetDateTime(0), Is.EqualTo(localDateTime.ToDateTimeUnspecified()));
Assert.That(reader.GetFieldValue <DateTime>(0), Is.EqualTo(localDateTime.ToDateTimeUnspecified()));
Assert.That(() => reader.GetFieldValue <ZonedDateTime>(0), Throws.TypeOf <InvalidCastException>());
#if NET6_0_OR_GREATER
Assert.That(() => reader.GetFieldValue <DateOnly>(0), Throws.TypeOf <InvalidCastException>());
#endif
// Internal PostgreSQL representation, for out-of-range values.
Assert.That(() => reader.GetInt64(0), Throws.Nothing);
}
void INpgsqlSimpleTypeHandler <LocalDateTime> .Write(LocalDateTime value, NpgsqlWriteBuffer buf, NpgsqlParameter parameter)
{
if (_integerFormat)
{
WriteInteger(value.InUtc().ToInstant(), buf);
}
else
{
WriteDouble(value.InUtc().ToInstant(), buf);
}
}
private void TimeToJSON()
{
Instant now = SystemClock.Instance.Now;
String timeZone = TimeZoneInfo.Local.DisplayName;
long ticks = now.Ticks;
String timeZoneStan = TimeZoneInfo.Local.StandardName;
String timeZoneId = TimeZoneInfo.Local.Id;
var nowUtc = now.InUtc();
DateTimeOffset odt = DateTimeOffset.Now;
DateTime dt = odt.DateTime;
DateTimeZoneProviders.Serialization = DateTimeZoneProviders.Bcl;
//var curZone = DateTimeZoneProviders.Tzdb["Canada/Eastern"];
var curZone = DateTimeZoneProviders.Bcl[timeZoneId];
var nowZone = now.InZone(curZone);
var offset = nowZone.ToOffsetDateTime();
var duration = NodaTime.Duration.FromMinutes(3);
var local = new LocalDateTime(nowZone.Year, nowZone.Month, nowZone.Day, nowZone.Hour, nowZone.Minute, nowZone.Second, nowZone.Millisecond);
var localInUtc = local.InUtc();
var inst = localInUtc.ToInstant();
var odtToStr = odt.ToString("yyyy-MM-ddTHH:mm:sszzz", CultureInfo.InvariantCulture);
var offsetToStr = offset.ToString("yyyy-MM-ddTHH:mm:ss.ffffffo<m>", CultureInfo.InvariantCulture);
String nowStr = JsonConvert.SerializeObject(now, Formatting.None, settings);
String offSet = JsonConvert.SerializeObject(offset, Formatting.Indented, settings);
String utc = JsonConvert.SerializeObject(nowUtc, Formatting.None, settings);
String dur = JsonConvert.SerializeObject(duration, Formatting.None, settings);
String loc = JsonConvert.SerializeObject(local, Formatting.None, settings);
String loc2Str = local.ToString();
//JSONText = String.Format("Time: {0}\nOffset: {1}\nUtc time: {2}\nZoned time: {3}\nTime zone name: {4}\nTime zone name standard: {5}\nLocal time: {6}\nTostr local: {7}\nTicks: {8}",
// nowStr, offSet, utc, nowZone, timeZone, timeZoneStan, loc, loc2Str, ticks);
JSONText = String.Format("Time: {0}\nOffset: {1}\nZoned time: {2}\nOffset To String: {3}",
nowStr, offSet, nowZone, offset.ToString());
}
public void Timestamp(LocalDateTime localDateTime)
{
using (var conn = OpenConnection())
{
var instant = localDateTime.InUtc().ToInstant();
conn.ExecuteNonQuery("CREATE TEMP TABLE data (d1 TIMESTAMP, d2 TIMESTAMP, d3 TIMESTAMP, d4 TIMESTAMP, d5 TIMESTAMP)");
using (var cmd = new NpgsqlCommand("INSERT INTO data VALUES (@p1, @p2, @p3, @p4, @p5)", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Timestamp)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter("p2", DbType.DateTime)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter("p3", DbType.DateTime2)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p4", Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p5", Value = localDateTime
});
cmd.ExecuteNonQuery();
}
// Make sure the values inserted are the good ones, textually
using (var cmd = new NpgsqlCommand("SELECT d1::TEXT, d2::TEXT, d3::TEXT, d4::TEXT, d5::TEXT FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
{
Assert.That(reader.GetValue(i), Is.EqualTo(instant.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'FFFFFF", CultureInfo.InvariantCulture)));
}
}
using (var cmd = new NpgsqlCommand("SELECT * FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(Instant)));
Assert.That(reader.GetFieldValue <Instant>(i), Is.EqualTo(instant));
Assert.That(reader.GetValue(i), Is.EqualTo(instant));
Assert.That(reader.GetFieldValue <LocalDateTime>(i), Is.EqualTo(localDateTime));
Assert.That(() => reader.GetFieldValue <ZonedDateTime>(i), Throws.TypeOf <InvalidCastException>());
Assert.That(() => reader.GetDateTime(i), Throws.TypeOf <InvalidCastException>());
Assert.That(() => reader.GetDate(i), Throws.TypeOf <InvalidCastException>());
}
}
}
}
private static ZonedDateTime GetJobsZonedDateTime(RecurringJobDto rJobDto, DateTimeZone hostDTZ)
{
LocalDateTime ldt = LocalDateTime.FromDateTime(rJobDto.NextExecution !.Value);
ZonedDateTime zdt = ldt.InUtc();
zdt = zdt.WithZone(hostDTZ);
return(zdt);
}
public void InUtc()
{
var local = new LocalDateTime(2009, 12, 22, 21, 39, 30);
var zoned = local.InUtc();
Assert.AreEqual(local, zoned.LocalDateTime);
Assert.AreEqual(Offset.Zero, zoned.Offset);
Assert.AreSame(DateTimeZone.Utc, zoned.Zone);
}
public UniformTimeSequence(LocalDateTime start, LocalDateTime end, TimePeriod period, bool exact = true)
{
Ensure.Bool.IsTrue(start < end);
Ensure.Bool.IsTrue(exact == true ? TimeInterval.StartOfInterval(start, period) == start : true);
Ensure.Bool.IsTrue(exact == true ? TimeInterval.StartOfInterval(end, period) == end : true);
_range = new ZonedDateTimeRange(
TimeInterval.StartOfInterval(start.InUtc(), period),
TimeInterval.StartOfInterval(end.InUtc(), period));
_period = period;
}
public Instant FromDb(DateTime dbValue)
{
var utcValue = new LocalDateTime(
dbValue.Year,
dbValue.Month,
dbValue.Day,
dbValue.Hour,
dbValue.Minute,
dbValue.Second,
dbValue.Millisecond);
return(utcValue.InUtc().ToInstant());
}
public Instant GetInstant(LocalDate?defaultDate = null, LocalTime?defaultTime = null)
{
LocalDate?date = string.IsNullOrEmpty(this.DateStr) ? defaultDate : LocalDatePattern.Iso.Parse(this.DateStr).Value;
LocalTime?time = string.IsNullOrEmpty(this.TimeStr) ? defaultTime : LocalTimePattern.ExtendedIso.Parse(this.TimeStr).Value;
if (date == null)
{
throw new Exception("No Date in event occurrence");
}
if (time == null)
{
throw new Exception("No Time in event occurrence");
}
LocalDateTime ldt = (LocalDate)date + (LocalTime)time;
return(ldt.InUtc().ToInstant());
}
/// <summary>
/// Determine the number of votes that each movie got and then select the highest ranked movie.
/// If there is a tie on more than one of the movies, message the movie night creator with an
/// embed where they will break the tie.
/// </summary>
/// <param name="movieNightId">ID for the movie night in the data store</param>
public async Task CalculateVotes(int movieNightId)
{
GuildMovieNight movieNight = await GetGuildMovieNightAsync(movieNightId);
(DiscordClient client, DiscordGuild guild, DiscordChannel channel) = await this.GetCommonDiscordObjects(movieNight);
DiscordMessage votingMessage = await channel.GetMessageAsync(movieNight.VotingMessageId ?? throw new Exception("Somehow, some way, the voting message id was null... something done f$*@ed up."));
Dictionary <string, DiscordReaction> mostReactedReactions = GetMostReactedReactons(votingMessage);
DiscordMember host = await guild.GetMemberAsync(movieNight.HostId);
GuildMovieSuggestion winningSuggestion = await GetWinningSuggestion(client, guild, host, movieNight, mostReactedReactions);
movieNight.WinningMovieImdbId = winningSuggestion.ImdbId;
DbResult movieNightUpdateResult = await this.mediator.Send(new GuildMovieNights.Update(movieNight));
if (!movieNightUpdateResult.Success)
{
throw new Exception("An error occurred in updating the movie night with the winning suggestion");
}
RecurringJobDto rJobDto = GetMovieNightStartRecurringJobInfo(movieNight);
LocalDateTime ldt = LocalDateTime.FromDateTime(rJobDto.NextExecution !.Value);
DateTimeZone hostDTZ = await GetUserDateTimeZone(movieNight.HostId);
ZonedDateTime zdt = ldt.InUtc();
zdt = zdt.WithZone(hostDTZ);
OmdbMovie movieInfo = await this.omdbClient.GetByImdbIdAsync(winningSuggestion.ImdbId, omdbPlotOption : OmdbPlotOption.SHORT);
DiscordEmbedBuilder announceWinnerEmbed = movieInfo.ToDiscordEmbedBuilder(true)
.WithAuthor(host.DisplayName, iconUrl: host.AvatarUrl);
DiscordMessageBuilder announceWinnerMessage = new DiscordMessageBuilder()
.WithContent($"@everyone, here's what {host.Mention} is showing {zdt.ToString("MM/dd/yyyy hh:mm x", null)}")
.WithEmbed(announceWinnerEmbed.Build());
await channel.SendMessageAsync(announceWinnerMessage);
}
protected override void Seed(NodaTimeContext context)
{
var localDateTime = new LocalDateTime(2018, 4, 20, 10, 31, 33, 666);
var zonedDateTime = localDateTime.InUtc();
var instant = zonedDateTime.ToInstant();
DefaultPeriod = Period.FromYears(2018) + Period.FromMonths(4) + Period.FromDays(20) +
Period.FromHours(10) + Period.FromMinutes(31) + Period.FromSeconds(23) +
Period.FromMilliseconds(666);
context.Add(new NodaTimeTypes
{
Id = 1,
LocalDateTime = localDateTime,
ZonedDateTime = zonedDateTime,
Instant = instant,
LocalDate = localDateTime.Date,
LocalTime = localDateTime.TimeOfDay,
OffsetTime = new OffsetTime(new LocalTime(10, 31, 33, 666), Offset.Zero),
Period = DefaultPeriod
});
context.SaveChanges();
}
public static void Seed(NodaTimeContext context)
{
var localDateTime = new LocalDateTime(2018, 4, 20, 10, 31, 33, 666);
var zonedDateTime = localDateTime.InUtc();
var instant = zonedDateTime.ToInstant();
_defaultPeriod = Period.FromYears(2018) + Period.FromMonths(4) + Period.FromDays(20) +
Period.FromHours(10) + Period.FromMinutes(31) + Period.FromSeconds(23) +
Period.FromMilliseconds(666);
context.Add(new NodaTimeTypes
{
Id = 1,
LocalDateTime = localDateTime,
ZonedDateTime = zonedDateTime,
Instant = instant,
LocalDate = localDateTime.Date,
LocalTime = localDateTime.TimeOfDay,
OffsetTime = new OffsetTime(new LocalTime(10, 31, 33, 666), Offset.Zero),
Period = _defaultPeriod,
DateRange = new NpgsqlRange <LocalDate>(localDateTime.Date, localDateTime.Date.PlusDays(5))
});
context.SaveChanges();
}
public static Instant Utc(this LocalDateTime localDateTime) => localDateTime.InUtc().ToInstant();
public static ZonedDateTime FromUtcToTimezone(this LocalDateTime localUtc, DateTimeZone timezone)
{
EnsureArg.IsNotNull(timezone);
return(localUtc.InUtc().WithZone(timezone));
}
public static LocalDateTime AtStartOfInterval(this LocalDateTime localTime, TimePeriod period)
{
return((localTime.InUtc() - _offsetFromStart(localTime.TimeOfDay, period)).LocalDateTime);
}
public static LocalDateTime StartOfInterval(LocalDateTime time, TimePeriod period)
{
return((time.InUtc() - _offsetFromStart(time.TimeOfDay, period)).LocalDateTime);
}
public static TimeInterval IntervalOf(LocalDateTime time, TimePeriod period)
{
return(new TimeInterval(StartOfInterval(time.InUtc(), period), period));
}
public void InUtc()
{
var local = new LocalDateTime(2009, 12, 22, 21, 39, 30);
var zoned = local.InUtc();
Assert.AreEqual(local, zoned.LocalDateTime);
Assert.AreEqual(Offset.Zero, zoned.Offset);
Assert.AreSame(DateTimeZone.Utc, zoned.Zone);
}
public void Timestamp(LocalDateTime localDateTime)
{
using (var conn = OpenConnection())
{
var instant = localDateTime.InUtc().ToInstant();
var minTimestampPostgres = Instant.FromUtc(-4713, 12, 31, 00, 00, 00);
var maxTimestampPostgres = Instant.MaxValue;
var dateTime = new DateTime(2020, 03, 04, 12, 20, 44, 0, DateTimeKind.Utc);
conn.ExecuteNonQuery("CREATE TEMP TABLE data (d1 TIMESTAMP, d2 TIMESTAMP, d3 TIMESTAMP, d4 TIMESTAMP, d5 TIMESTAMP, d6 TIMESTAMP, d7 TIMESTAMP, d8 TIMESTAMP)");
using (var cmd = new NpgsqlCommand("INSERT INTO data VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Timestamp)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter("p2", DbType.DateTime)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter("p3", DbType.DateTime2)
{
Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p4", Value = instant
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p5", Value = localDateTime
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p6", Value = minTimestampPostgres
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p7", Value = maxTimestampPostgres
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p8", Value = dateTime
});
cmd.ExecuteNonQuery();
}
// Make sure the values inserted are the good ones, textually
using (var cmd = new NpgsqlCommand("SELECT d1::TEXT, d2::TEXT, d3::TEXT, d4::TEXT, d5::TEXT FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
{
Assert.That(reader.GetValue(i), Is.EqualTo(instant.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'FFFFFF", CultureInfo.InvariantCulture)));
}
}
using (var cmd = new NpgsqlCommand("SELECT d6::TEXT, d7::TEXT, d8::TEXT FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader.GetValue(0), Is.EqualTo("4714-12-31 00:00:00 BC"));
Assert.That(reader.GetValue(1), Is.EqualTo(maxTimestampPostgres.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'FFFFFF", CultureInfo.InvariantCulture)));
Assert.That(reader.GetValue(2), Is.EqualTo("2020-03-04 12:20:44"));
}
using (var cmd = new NpgsqlCommand("SELECT d1, d2, d3, d4, d5 FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(Instant)));
Assert.That(reader.GetFieldValue <Instant>(i), Is.EqualTo(instant));
Assert.That(reader.GetValue(i), Is.EqualTo(instant));
Assert.That(reader.GetFieldValue <LocalDateTime>(i), Is.EqualTo(localDateTime));
Assert.That(() => reader.GetFieldValue <ZonedDateTime>(i), Throws.TypeOf <InvalidCastException>());
Assert.That(() => reader.GetDateTime(i), Is.EqualTo(localDateTime.ToDateTimeUnspecified()));
Assert.That(() => reader.GetFieldValue <DateTime>(i), Is.EqualTo(localDateTime.ToDateTimeUnspecified()));
Assert.That(() => reader.GetDate(i), Throws.TypeOf <InvalidCastException>());
}
}
}
}
void INpgsqlSimpleTypeHandler <LocalDateTime> .Write(LocalDateTime value, NpgsqlWriteBuffer buf, NpgsqlParameter?parameter) => WriteInteger(value.InUtc().ToInstant(), buf);
public static ZonedDateTime FromUtcToTimezone(this LocalDateTime localUtc, string timezoneName)
{
var timezone = DateTimeZoneProviders.Tzdb[timezoneName];
return(localUtc.InUtc().WithZone(timezone));
}
